Fetch places from POSTGIS

Question

I have downloaded data from geonames and then followed the below link to dump it into POSTGIS.

https://github.com/colemanm/gazetteer/blob/master/docs/geonames_postgis_import.md

I'm not sure how to get places between two routes at the moment. The table has separate columns for latitude and longitude.

I need to get places between the route, is there anyway of doing it?

WHAT I HAVE TRIED

Get places between two points :

SELECT * FROM geoname WHERE 
ST_DWithin('POINT(44.8781 -116.6298)', 
ST_GeographyFromText('POINT(43.7392 -114.9903)'), 16093.4);

I'm getting 0 results, even though I'm sure there are places near these points.

Also I have tried,

SELECT * FROM geoname WHERE 
ST_Distance_Sphere('POINT(44.8781 -116.6298)',     
ST_MakePoint(43.7392,-114.9903)) <= 16093.4;

Is it something to be done using pgRouting. May be something like get a route using pgRouting and then get the places along the path.

Mike T Mike T · Accepted Answer · 2017-02-06T23:58:58

Did you not see the error message "ERROR: Coordinate values are out of range [-180 -90, 180 90] for GEOGRAPHY type"?

Swap the axis order from lat lon (or Y X) to the correct axis order lon lat (or X Y). If you mistakenly created geometry/geography objects with the former axis order, you can update a swapped version with ST_FlipCoordinates

Fetch places from POSTGIS

1 Answers