英文:
How can I compare two coordinates to see if they are in the same country in BigQuery?
问题
我在BigQuery中有一个包含经度和纬度列的表格。
我还有另一个表格，包含所有国家及其对应的坐标信息。
我想要比较城市A的坐标，看它是否与某个国家的坐标匹配。
我尝试过以下代码，其中geography_2是秘鲁作为一个国家的地理对象（由坐标创建），而geography_1是秘鲁内部某个区域的地理对象：
我尝试比较这两者，但结果返回false。
SELECT ST_COVERS(geography_1, geography_2)
FROM (SELECT ST_GEOGPOINT(-79.036889, -8.123089) as geography_1,
ST_GEOGPOINT(-75.015152, -9.189967) as geography_2) --秘鲁
我也尝试过使用ST_WITHIN和ST_INTERSECTS，但都没有成功。
英文:
I have 1 table in BigQuery with longitude and latitude columns.
I have another table that has all the countries and their corresponding coordinates.
How can I compare city A's coordinate to see if it matches a country's coordinate?
I tried this, where geography_2 is the Geography object for Peru as a country (created from the coordinates) while geography_1 is the Geography for an area within Peru:
I tired to compare both but it returns false.
SELECT ST_COVERS(geography_1, geography_2)
FROM (SELECT ST_GEOGPOINT(-79.036889, -8.123089) as geography_1,
ST_GEOGPOINT(-75.015152, -9.189967) as geography_2) --peru
I have tried ST_WITHIN and ST_INTERSECTS also, but no luck.
答案1
得分: 1
"-75.015152, -9.189967" 不代表秘鲁，它只是秘鲁某个地方的一个单一点，可能是质心，但它不描述秘鲁的边界，也不足以确定一个点是否在秘鲁境内。
你还可以查看你尝试使用的函数的定义：
ST_WITHIN函数在点在多边形内部时返回 true。这是用来确定一个点是否在国家边界内（边界被描述为多边形）的正确函数。但你没有多边形 - 你只有一个单一点。ST_INTERSECTS函数在形状相交，即具有共同点时返回 true。在两个点作为参数的情况下，只有当两个点相同时才可能发生，即它只适用于与用来“代表”秘鲁的点完全相同的情况。
要确定一个国家，你需要一个国家边界的描述，以多边形的形式。在BigQuery中，你可以使用的一个数据集是OSM数据。查看如何使用它以及如何确定国家的方法在这个最新的问题中：
英文:
-75.015152, -9.189967 does not represent Peru, it is a single point somewhere in Peru, probably centroid - but it does not describe Peru boundaries, and is not enough to establish if a point is within Peru.
You can also check the definitions of the functions you tried using:
ST_WITHINreturns true if a point is within a polygon. That's a right function to use to see if a point is within country boundaries (with boundaries described as polygon). But you don't have a polygon - you have a single point.ST_INTERSECTSreturns true if the shapes intersect, i.e. have common points. With two points as arguments, this is only possible if the two points are the same, i.e. it only works for exactly the same point as the one used to "represent" Peru.
To determine country you need a description of the country boundary, as a polygon. One dataset you can use in BigQuery is OSM data. See how to use it and how to determine a country in a recent question:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论