选择google bigquery中lat lon所在的多边形的id

0g0grzrc 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(345)

我试图得到一个特定坐标所在的多边形的id。我有两个表，一个表存储lat lon，另一个表存储多边形（lon1 lat1，lon2 lat2….）。我在mssql中经常使用这个方法，但是在将同一个查询转换为bigquery时遇到了一些问题
我至今的努力：

MS-SQL

SELECT  DISTINCT ID, P.ID AS POLYGON_ID
FROM LOCATIONS as L
LEFT OUTER JOIN POLYGONS as P ON 
ON P.POLYGON.STIntersects(geography::Point(L.[LAT], L.[LON], 4326)) = 1

BigQuery

SELECT DISTINCT ID,p.id as POLYGON_ID
from locations L
LEFT OUTER JOIN polygons p
ON p.polygon.ST_INTERSECTION(ST_GEOGPOINT(l.lon, l.lat)) =1

欢迎任何能指导我的例子或材料。谢谢

sql google-bigquery intersection

来源：https://stackoverflow.com/questions/63099388/select-id-of-polygon-in-which-a-lat-lon-lies-in-google-bigquery

2条答案

按热度按时间

piv4azn71#

下面是bigquery标准sql


# standardSQL

SELECT l.id, p.id AS polygon_id
FROM (
  SELECT id, ST_GEOGPOINT(lon, lat) geo
  FROM `project.dataset.locations`
) l
JOIN (
  SELECT id, ST_GEOGFROMTEXT('POLYGON((' || polygon || '))') geo
  FROM `project.dataset.polygons`
) p  
ON ST_WITHIN(l.geo, p.geo)

您可以使用下面的示例中的示例/虚拟数据来测试、播放上述内容


# standardSQL

WITH `project.dataset.polygons` AS (
  SELECT 1 id, '1 1, 1 2, 2 2, 2 1, 1 1' AS polygon UNION ALL
  SELECT 2, '3 3, 3 4, 4 4, 4 3, 3 3'
), `project.dataset.locations` AS (
  SELECT 1 id, 1.5 lon, 1.5 lat UNION ALL
    SELECT 2, 3.5, 3.5 
)
SELECT l.id, p.id AS polygon_id
FROM (
  SELECT id, ST_GEOGPOINT(lon, lat) geo
  FROM `project.dataset.locations`
) l
JOIN (
  SELECT id, ST_GEOGFROMTEXT('POLYGON((' || polygon || '))') geo
  FROM `project.dataset.polygons`
) p  
ON ST_WITHIN(l.geo, p.geo)

有输出

Row id  polygon_id   
1   1   1    
2   2   2

赞(0）回复(0）举报 2021-07-29

ecfsfe2w2#

SELECT L.ID, P.ID AS POLYGON_ID
FROM LOCATIONS as L
JOIN POLYGONS as P ON 
ST_INTERSECTS(ST_GEOGPOINT(L.lon,L.lat),P.polygon)

哪里 P.polygon 以以下格式存储 (lon_1 lat_1, lon_2 lat_2 .....lon_n, lat_n) .

赞(0）回复(0）举报 2021-07-29

我来回答

选择google bigquery中lat lon所在的多边形的id

2条答案

相关问题

热门标签

最新问答