3

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);
Improve this question
5
  • 1
    Why have you projected your points to EPSG:32661? why not one of the UTM projection? – Devdatta Tengshe Apr 23 '13 at 4:39
  • It is all of the US zipcodes. I have retrieved data from this link census.gov/geo/maps-data/data/gazetteer2010.html GEOID POP10 HU10 ALAND AWATER ALAND_SQMI AWATER_SQMI INTPTLAT INTPTLONG 00601 18570 7744 166659789 799296 64.348 0.309 18.180555 -66.749961 Here is a row of the data. From here, I need to convert tihs lat/long to geometry with respect to an SRID. Please advise, thanks – user17473 Apr 23 '13 at 15:09
  • This should be a comment to Devdatta's answer. – user3461 Apr 23 '13 at 15:15
  • yes. for some reason, I could not add a comment to his last reply. The dynamic gui did not load the button – user17473 Apr 23 '13 at 15:20
  • You may not have been able to post a comment because you were logged on as a different account. You have two: christopher smith and Christopher Smith. Note the different ID numbers in the links. A moderator has since moved the answer to a comment. – user3461 Apr 23 '13 at 16:52
1

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.

Improve this answer
5
  • Thanks for the reply. I am new to the detailed concepts of mapping projecting. – christopher smith Apr 23 '13 at 13:10
  • Thanks for the reply. I am new to the detailed concepts of mapping projection. I humbly request a tad bit more help... If I have a data set of lat,long, when I create the geometry, I need to chose an srid. Can you walk me through this process? I ended up getting ERROR: Input geometry has unknown (0) SRID ********** Error ********** – christopher smith Apr 23 '13 at 13:20
  • What is the area of your data? Whole of US? or what? – Devdatta Tengshe Apr 23 '13 at 13:22
  • It is all of the US zipcodes. I have retrieved data from this link census.gov/geo/maps-data/data/gazetteer2010.html GEOID POP10 HU10 ALAND AWATER ALAND_SQMI AWATER_SQMI INTPTLAT INTPTLONG 00601 18570 7744 166659789 799296 64.348 0.309 18.180555 -66.749961 Here is a row of the data. From here, I need to convert tihs lat/long to geometry with respect to an SRID. Please advise, thanks – christopher smith Apr 24 '13 at 1:54
  • I could try answering this, but I'm not an expert on US projection, so I may give you wrong information . I'll suggest that you post a new question: "Which Projection (with wkid) should I use for the US, to calculate distances?". That way you'll get more authoritative answers. – Devdatta Tengshe Apr 24 '13 at 4:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.