I am using PostGis to search for all points within a given range, relative to a given origin. I am looking at all the documentation provided by PostGis but can’t seem to figure out how would I come up with a solution for this?
My location table looks like this:
Id - INT(11) Longitude - DOUBLE Latitude - DOUBLE Address - LONGTEXT City - LONGTEXT Region - LONGTEXT Country - LONGTEXT
The inputs would be Longitude, Latitude and a Range (as in kilometers or miles).
What I’m hoping is to receive an output of all the records that are within the Range.
Advertisement
Answer
Welcome to SO.
PostGIS relies on coordinates embedded in fields of type geometry or geography. That being said, your table structure shouldn’t store the coordinates into two distinct double precision fields, but in a single one.
If you still can change the table structure, just add a new geometry column into your table:
SELECT AddGeometryColumn ('your_schema','your_table','geom',4326,'POINT',2);
And to create geometries out of your longitude and latitude columns you can do something like this:
UPDATE TABLE your_table SET geom = ST_MakePoint(Longitude, Latitude);
After that you might want to create an index, e.g. ..
CREATE INDEX my_index ON my_table USING GIST (geom);
.. and query it using ST_DWithin
SELECT * FROM your_table
WHERE ST_DWithin(geom,
ST_MakePoint(input_lon, input_lat),input_distance)
Keep in mind that geometry and geography have different units of measurement. See this answer or the documentation for more details.
If you’re not able to change the table structure, you’d have to stick to the solution posted by @Michael Entin, but keep in mind that there will be an unnecessary overhead to create geometry or geography values in query time, and you also will not be able to index them, possibly making queries much slower!
Good luck.
Further reading: