[GIS] postgis distance calculation between points with lat, long coordinates

convertdistancepostgis

Team,

I am new to postgis, but a postgres user for quite a while. I would like to compare

the geospatial capabilities between postgresql and mongodb.

I have the lat.lon, zip code of US locations;

I ran across this blog entry at http://unserializableone.blogspot.com/2007/02/using-postgis-to-find-points-of.html

Also, I see this the function ST_DWithin

SELECT * FROM geotable 
WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0);

Please advise,

I see that the lat/lon must be converted into points; but I am having issues with the best mechanism to perform indexed distance calculations.

Update..

I have made progress and loaded a spatial aware table .. see below

create table public.disttest
(

    zipcode varchar(5),
    state varchar(2),
    city varchar(55),
    long numeric,
    lat numeric

);

SELECT AddGeometryColumn( 'public', 'disttest', 'geom', 32661, 'POINT', 2 );

COPY public.disttest FROM 'c:\temp\zips.txt'

select * from public.disttest limit 10;


UPDATE public.disttest SET geom = ST_Transform(ST_PointFromText('POINT(' || long || ' ' || lat || ')',4269),32661) ;

Now the question is how to use the below type query for a 10 mile radiuus

SELECT * FROM public.disttest WHERE ST_DWithin(geom, geom, 16093);

Best Answer

As Indicated in my comment, I am not sure why you have chosen EPSG:32661. I would have chosen a UTM projection.

However, the actual usage of the query is quite simple.

ST_DWithin requires three inputs. The First two are geometries, and the third is the distance. If you use it in a query, the first geometry comes from your table, and the second geometry is the search geometry.

For example, if you want to search within 10 miles of a point with latitude=y, and longitude =x, the following query can be used

SELECT * FROM public.disttest WHERE ST_DWithin(geom, 
                           ST_Transform(ST_MakePoint(x, y),4326),32661), 16093);

You need to make sure that the projection of your data, and the projection wkid used in the query are the same.

Related Question