Skip to content

Instantly share code, notes, and snippets.

@migurski

migurski/places.sql

Last active August 1, 2023 16:43
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save migurski/e2436a452889e107f3916af81ff4808e to your computer and use it in GitHub Desktop.
Save migurski/e2436a452889e107f3916af81ff4808e to your computer and use it in GitHub Desktop.
Download ZIP
Overture query examples for five major world cities
Raw
places.sql
SELECT
categories.main AS main_category,
names['common'][1]['value'] AS common_name,
confidence,
ST_AsText(ST_GeomFromBinary(geometry)) AS wkt,
confidence,
CASE WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((0.905301687393953 47.895747559191,3.79669831260605 47.895747559191,3.79669831260605 49.79817987169,0.905301687393953 49.79817987169,0.905301687393953 47.895747559191))')) THEN 'Paris'
WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((75.7827016873939 27.3583247590675,78.674098312606 27.3583247590675,78.674098312606 29.8959350440702,75.7827016873939 29.8959350440702,75.7827016873939 27.3583247590675))')) THEN 'New Delhi'
WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((138.325301687394 34.5021508791562,141.216698312606 34.5021508791562,141.216698312606 36.8505684661713,138.325301687394 36.8505684661713,138.325301687394 34.5021508791562))')) THEN 'Tokyo'
WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((-75.445698312606 39.6122469253799,-72.5543016873939 39.6122469253799,-72.5543016873939 41.8037198024631,-75.445698312606 41.8037198024631,-75.445698312606 39.6122469253799))')) THEN 'New York'
WHEN ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('POLYGON ((-78.483698312606 37.7609292364415,-75.5923016873939 37.7609292364415,-75.5923016873939 40.0112467698912,-78.483698312606 40.0112467698912,-78.483698312606 37.7609292364415))')) THEN 'Washington DC'
END as city_name
FROM "daylight"."overture_places"
WHERE ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('MULTIPOLYGON (((78.5640510812028 28.148102069581,78.430454215489 27.927493915808,78.2506630803937 27.7337581233952,78.0315869484106 27.5745054231637,77.7816447924325 27.4560200255608,77.5104417493445 27.3829936483315,77.2284 27.3583247590675,76.9463582506555 27.3829936483315,76.6751552075675 27.4560200255608,76.4252130515894 27.5745054231637,76.2061369196063 27.7337581233952,76.026345784511 27.927493915808,75.8927489187972 28.148102069581,75.8104803750922 28.3869627545292,75.7827016873939 28.6348,75.8104803750922 28.8820532860606,75.8927489187972 29.1192510421046,76.026345784511 29.3373705763081,76.2061369196063 29.5281710412208,76.4252130515894 29.6844886298006,76.6751552075675 29.8004859289385,76.9463582506555 29.8718498983678,77.2284 29.8959350440702,77.5104417493445 29.8718498983678,77.7816447924325 29.8004859289385,78.0315869484106 29.6844886298007,78.2506630803937 29.5281710412208,78.430454215489 29.3373705763081,78.5640510812028 29.1192510421046,78.6463196249078 28.8820532860606,78.674098312606 28.6348,78.6463196249078 28.3869627545292,78.5640510812028 28.148102069581)),((141.106651081203 35.2343712281358,140.973054215489 35.0299620571239,140.793263080394 34.8503744202711,140.574186948411 34.7026971432171,140.324244792432 34.5927919913612,140.053041749344 34.5250402887433,139.771 34.5021508791562,139.488958250655 34.5250402887433,139.217755207567 34.5927919913612,138.967813051589 34.7026971432171,138.748736919606 34.8503744202711,138.568945784511 35.0299620571239,138.435348918797 35.2343712281358,138.353080375092 35.4555868546104,138.325301687394 35.685,138.353080375092 35.9137553403255,138.435348918797 36.1330977395238,138.568945784511 36.3347035468673,138.748736919606 36.5109845743056,138.967813051589 36.6553554405523,139.217755207567 36.7624577082197,139.488958250655 36.8283366630412,139.771 36.8505684661713,140.053041749344 36.8283366630412,140.324244792432 36.7624577082197,140.574186948411 36.6553554405523,140.793263080394 36.5109845743056,140.973054215489 36.3347035468673,141.106651081203 36.1330977395239,141.188919624908 35.9137553403255,141.216698312606 35.685,141.188919624908 35.4555868546104,141.106651081203 35.2343712281358)),((-75.7023489187972 38.4631068423783,-75.835945784511 38.2671345511738,-76.0157369196063 38.0949273639693,-76.2348130515894 37.9532966911579,-76.4847552075675 37.8478784051015,-76.7559582506555 37.7828870504778,-77.038 37.7609292364415,-77.3200417493445 37.7828870504778,-77.5912447924325 37.8478784051015,-77.8411869484106 37.9532966911579,-78.0602630803937 38.0949273639693,-78.240054215489 38.2671345511738,-78.3736510812028 38.4631068423782,-78.4559196249078 38.6751484350964,-78.483698312606 38.895,-78.4559196249078 39.1141730883208,-78.3736510812028 39.3242825827493,-78.240054215489 39.5173633938855,-78.0602630803937 39.6861600163451,-77.8411869484106 39.8243803068406,-77.5912447924325 39.9269075532516,-77.3200417493445 39.9899672500086,-77.038 40.0112467698912,-76.7559582506555 39.9899672500086,-76.4847552075675 39.9269075532516,-76.2348130515894 39.8243803068406,-76.0157369196063 39.6861600163451,-75.835945784511 39.5173633938855,-75.7023489187972 39.3242825827494,-75.6200803750922 39.1141730883209,-75.5923016873939 38.895,-75.6200803750922 38.6751484350964,-75.7023489187972 38.4631068423783)),((-72.797945784511 40.1054514857134,-72.9777369196063 39.9376825952034,-73.1968130515894 39.7996901730893,-73.4467552075675 39.6969728592395,-73.7179582506555 39.6336436790455,-74 39.6122469253799,-74.2820417493445 39.6336436790455,-74.5532447924325 39.6969728592395,-74.8031869484106 39.7996901730893,-75.0222630803937 39.9376825952034,-75.202054215489 40.1054514857134,-75.3356510812028 40.2963535561639,-75.4179196249078 40.5028859553105,-75.445698312606 40.717,-75.4179196249078 40.93042760842,-75.3356510812028 41.1350052396213,-75.202054215489 41.322981895869,-75.0222630803937 41.4873001834273,-74.8031869484106 41.6218421740175,-74.5532447924325 41.7216344875275,-74.2820417493445 41.7830093136393,-74 41.8037198024631,-73.7179582506555 41.7830093136393,-73.4467552075675 41.7216344875275,-73.1968130515894 41.6218421740175,-72.9777369196063 41.4873001834274,-72.797945784511 41.322981895869,-72.6643489187972 41.1350052396213,-72.5820803750922 40.93042760842,-72.5543016873939 40.717,-72.5820803750922 40.5028859553105,-72.6643489187972 40.2963535561639,-72.797945784511 40.1054514857134)),((3.55305421548899 48.3247485280962,3.37326308039369 48.1788750551085,3.15418694841059 48.0588488190902,2.9042447924325 47.9694796451953,2.63304174934449 47.9143693108282,2.351 47.895747559191,2.06895825065552 47.9143693108282,1.7977552075675 47.9694796451953,1.54781305158942 48.0588488190902,1.32873691960632 48.1788750551085,1.14894578451101 48.3247485280962,1.01534891879717 48.4906663278232,0.933080375092202 48.670085522485,0.905301687393953 48.856,0.9330803750922 49.041226575226,1.01534891879717 49.2186868142228,1.148945784511 49.3816728968513,1.32873691960631 49.5240882773611,1.54781305158941 49.640656533071,1.79775520756749 49.7270942606687,2.06895825065551 49.7802459101722,2.35099999999999 49.79817987169,2.63304174934448 49.7802459101722,2.90424479243249 49.7270942606687,3.15418694841058 49.640656533071,3.37326308039368 49.5240882773611,3.55305421548899 49.3816728968513,3.68665108120283 49.2186868142228,3.7689196249078 49.041226575226,3.79669831260605 48.856,3.7689196249078 48.670085522485,3.68665108120283 48.4906663278232,3.55305421548899 48.3247485280962)))'))
AND confidence > 0.8
--limit 10;
Raw
transportation.sql
SELECT
type,
subtype,
json_extract_scalar(road, '$.class') as road_class,
ST_GeometryType(ST_GeomFromBinary(geometry)) as geometry_type,
ST_AsText(ST_GeomFromBinary(geometry)) as wkt
FROM "daylight"."overture_transportation"
WHERE ST_Within(ST_GeomFromBinary(geometry), ST_GeometryFromText('MULTIPOLYGON (((78.5640510812028 28.148102069581,78.430454215489 27.927493915808,78.2506630803937 27.7337581233952,78.0315869484106 27.5745054231637,77.7816447924325 27.4560200255608,77.5104417493445 27.3829936483315,77.2284 27.3583247590675,76.9463582506555 27.3829936483315,76.6751552075675 27.4560200255608,76.4252130515894 27.5745054231637,76.2061369196063 27.7337581233952,76.026345784511 27.927493915808,75.8927489187972 28.148102069581,75.8104803750922 28.3869627545292,75.7827016873939 28.6348,75.8104803750922 28.8820532860606,75.8927489187972 29.1192510421046,76.026345784511 29.3373705763081,76.2061369196063 29.5281710412208,76.4252130515894 29.6844886298006,76.6751552075675 29.8004859289385,76.9463582506555 29.8718498983678,77.2284 29.8959350440702,77.5104417493445 29.8718498983678,77.7816447924325 29.8004859289385,78.0315869484106 29.6844886298007,78.2506630803937 29.5281710412208,78.430454215489 29.3373705763081,78.5640510812028 29.1192510421046,78.6463196249078 28.8820532860606,78.674098312606 28.6348,78.6463196249078 28.3869627545292,78.5640510812028 28.148102069581)),((141.106651081203 35.2343712281358,140.973054215489 35.0299620571239,140.793263080394 34.8503744202711,140.574186948411 34.7026971432171,140.324244792432 34.5927919913612,140.053041749344 34.5250402887433,139.771 34.5021508791562,139.488958250655 34.5250402887433,139.217755207567 34.5927919913612,138.967813051589 34.7026971432171,138.748736919606 34.8503744202711,138.568945784511 35.0299620571239,138.435348918797 35.2343712281358,138.353080375092 35.4555868546104,138.325301687394 35.685,138.353080375092 35.9137553403255,138.435348918797 36.1330977395238,138.568945784511 36.3347035468673,138.748736919606 36.5109845743056,138.967813051589 36.6553554405523,139.217755207567 36.7624577082197,139.488958250655 36.8283366630412,139.771 36.8505684661713,140.053041749344 36.8283366630412,140.324244792432 36.7624577082197,140.574186948411 36.6553554405523,140.793263080394 36.5109845743056,140.973054215489 36.3347035468673,141.106651081203 36.1330977395239,141.188919624908 35.9137553403255,141.216698312606 35.685,141.188919624908 35.4555868546104,141.106651081203 35.2343712281358)),((-75.7023489187972 38.4631068423783,-75.835945784511 38.2671345511738,-76.0157369196063 38.0949273639693,-76.2348130515894 37.9532966911579,-76.4847552075675 37.8478784051015,-76.7559582506555 37.7828870504778,-77.038 37.7609292364415,-77.3200417493445 37.7828870504778,-77.5912447924325 37.8478784051015,-77.8411869484106 37.9532966911579,-78.0602630803937 38.0949273639693,-78.240054215489 38.2671345511738,-78.3736510812028 38.4631068423782,-78.4559196249078 38.6751484350964,-78.483698312606 38.895,-78.4559196249078 39.1141730883208,-78.3736510812028 39.3242825827493,-78.240054215489 39.5173633938855,-78.0602630803937 39.6861600163451,-77.8411869484106 39.8243803068406,-77.5912447924325 39.9269075532516,-77.3200417493445 39.9899672500086,-77.038 40.0112467698912,-76.7559582506555 39.9899672500086,-76.4847552075675 39.9269075532516,-76.2348130515894 39.8243803068406,-76.0157369196063 39.6861600163451,-75.835945784511 39.5173633938855,-75.7023489187972 39.3242825827494,-75.6200803750922 39.1141730883209,-75.5923016873939 38.895,-75.6200803750922 38.6751484350964,-75.7023489187972 38.4631068423783)),((-72.797945784511 40.1054514857134,-72.9777369196063 39.9376825952034,-73.1968130515894 39.7996901730893,-73.4467552075675 39.6969728592395,-73.7179582506555 39.6336436790455,-74 39.6122469253799,-74.2820417493445 39.6336436790455,-74.5532447924325 39.6969728592395,-74.8031869484106 39.7996901730893,-75.0222630803937 39.9376825952034,-75.202054215489 40.1054514857134,-75.3356510812028 40.2963535561639,-75.4179196249078 40.5028859553105,-75.445698312606 40.717,-75.4179196249078 40.93042760842,-75.3356510812028 41.1350052396213,-75.202054215489 41.322981895869,-75.0222630803937 41.4873001834273,-74.8031869484106 41.6218421740175,-74.5532447924325 41.7216344875275,-74.2820417493445 41.7830093136393,-74 41.8037198024631,-73.7179582506555 41.7830093136393,-73.4467552075675 41.7216344875275,-73.1968130515894 41.6218421740175,-72.9777369196063 41.4873001834274,-72.797945784511 41.322981895869,-72.6643489187972 41.1350052396213,-72.5820803750922 40.93042760842,-72.5543016873939 40.717,-72.5820803750922 40.5028859553105,-72.6643489187972 40.2963535561639,-72.797945784511 40.1054514857134)),((3.55305421548899 48.3247485280962,3.37326308039369 48.1788750551085,3.15418694841059 48.0588488190902,2.9042447924325 47.9694796451953,2.63304174934449 47.9143693108282,2.351 47.895747559191,2.06895825065552 47.9143693108282,1.7977552075675 47.9694796451953,1.54781305158942 48.0588488190902,1.32873691960632 48.1788750551085,1.14894578451101 48.3247485280962,1.01534891879717 48.4906663278232,0.933080375092202 48.670085522485,0.905301687393953 48.856,0.9330803750922 49.041226575226,1.01534891879717 49.2186868142228,1.148945784511 49.3816728968513,1.32873691960631 49.5240882773611,1.54781305158941 49.640656533071,1.79775520756749 49.7270942606687,2.06895825065551 49.7802459101722,2.35099999999999 49.79817987169,2.63304174934448 49.7802459101722,2.90424479243249 49.7270942606687,3.15418694841058 49.640656533071,3.37326308039368 49.5240882773611,3.55305421548899 49.3816728968513,3.68665108120283 49.2186868142228,3.7689196249078 49.041226575226,3.79669831260605 48.856,3.7689196249078 48.670085522485,3.68665108120283 48.4906663278232,3.55305421548899 48.3247485280962)))'))
--limit 50;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment