I have table
location with column
geo_data with DDL:
CREATE TABLE location ( id INTEGER PRIMARY KEY NOT NULL, geo_data GEOGRAPHY(GEOMETRY,4326), ); CREATE INDEX index_location_on_geo_data ON location USING GIST (geo_data);
And I use this query to find nearest location to given point:
SELECT ST_AsText(location.geo_data), st_distance(ST_GeomFromText('POINT(37.611100 55.756926)', 4326), location.geo_data) as distance FROM location WHERE ST_Dwithin(ST_GeomFromText('POINT(37.611100 55.756926)', 4326), location.geo_data, 1200) ORDER BY distance LIMIT 1;
And it works great but I confused by two issues about this query:
- There is no functions about to just find one closest location to given point and I should use
sort bydistance and
- In order to work around above limitation I should define my point twice (first in select statement in order to sort result set and second in
whereclause within function
ST_Dwithin). And this fact of duplication is also confusing me.
Is there any solutions to eliminate this drawbacks? Or maybe make this solution more faster.