Home How to find city name from latitude and longitude in Postgresql?
Reply: 1

How to find city name from latitude and longitude in Postgresql?

Kaustabh Datta Choudhury
1#
Kaustabh Datta Choudhury Published in 2017-12-07 06:11:13Z

I have a database which has latitudes and longitudes of various properties stored. I want to find out, which city does each of these properties belong to (all properties are in the US).

lat long
2#
lat long Reply to 2017-12-07 08:36:49Z

Talking about Postgresql, first of all you need to get a data of US cities boundaries shape file. Possible sites are

https://www.census.gov/geo/maps-data/data/tiger.html  
https://www.census.gov/geo/maps-data/data/tiger-cart-boundary.html
https://catalog.data.gov/dataset?tags=cities

After that import data into postgres. I am assuming that your properties data is already stored in postgres. Make sure the SRID geometry type of cities boundaries is 4326. if not, you can convert it easily with ST_transform function.

Finally, to check which city some specific lat/long falls in, you need to convert the lat/long into point geometry and check against the cities data. e.g it would be some thing like this

SELECT c.city_name FROM cities_boundaries AS c, properties AS p
WHERE ST_CONTAINS(c.geom, ST_SetSRID(ST_MakePoint(p.longitude, p.latitude), 4326))  
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.341509 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO