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);
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.
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);