[postgis-users] -- Still having trouble retrieving properties 100 meters around a point --

Rafael Orta rorta_us at yahoo.com
Tue Sep 19 21:23:52 PDT 2006


Hi Everybody
   
  I am new to Postgress and to the spatial world. I have a series of properties with
  their latitude and longitude and I need to be able to search and return the properties 100 meters around a point ( I need to use meters or feets). Below you will find what I am doing:
   
  Table creation:
  ===========
  create table smarter.vow_properties (property_id serial, address varchar(150), latitude numeric (7,4) , longitude numeric (7,4))
   
  Addition of geometric column:
  =======================
select AddGeometryColumn('smarter','vow_properties','geom',32011,'POINT',2);
   
  NOTE: I do not really understand the concept of SRID but as per research and advice
  from other people in the list I did pick the SRID 32011 becaue I live in NJ and the unit I need is meters. But I will appreciate if somebody could double check this or provide me some information about how to determinate the SRID is should use. I will be working with address all around the US but only the US and I need the units to come in meters or feets.
   
  Insertion of rows:
  ======================
  insert into smarter.vow_properties(address, latitude, longitude) values ('12 LONG BRIDGE DR,MOUNT LAUREL, NJ 08054','39.940252','-074.921916');
insert into smarter.vow_properties(address, latitude, longitude) values ('6 LONG BRIDGE DR, MOUNT LAUREL, NJ 08054','39.939777','-074.922529');
insert into smarter.vow_properties(address, latitude, longitude) values ('12 BARTON WAY, MOUNT LAUREL, NJ 08054','39.940323','-074.920154');

  Update of the geometric column
  =========================
  The following statement does not work, not sure why
   
  update smarter.vow_properties set geom = makePoint (longitude, latitude);

  This one doe work and I am using it
   
  update smarter.vow_properties set geom = GeometryFromText('POINT(' || latitude || ' ' ||longitude || ')', 32011);
   
  Search
  ========================
   
  The search below suppose to retrieve properties 100 meters around the point
  logitude = -074.905868 latitude = 39.940252
   
  select * from smarter.vow_properties where geom && Expand(GeomFromText('POINT(-074.905868 39.940252)',32011),100)
and Distance (GeomFromText('POINT(-074.905868 39.946788 )',32011),geom) < 100;

  Assuming that I am working with the right units (if I am not please advice what I need to do but keep in mind I know 0 about spatial) that query should have returned several units however it does not return any. Could somebody give a look and let me know what I am doing wrong? Once again please be aware that I have no previous knowledge of spatial, I am actually an Oracle DBA learning Postgress
   
  I will buy you a beer for you help if you ever come around south jersey :-)
   
  Rafael Orta



***************************************************** 
Rafael Orta                                  Phone: (609)-330-3193 
Sr. Oracle DBA                            Fax: (856)-642-7679 
Technical Manager 
Oraprofessionals                          Email: rorta_us at yahoo.com 
******************************************************
 		
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates starting at 1¢/min.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060919/1639c109/attachment.html>


More information about the postgis-users mailing list