Opened 9 years ago

Closed 9 years ago

#2363 closed defect (invalid)

st_distance with an added Integer value is not working in 2.0

Reported by: vbalent Owned by: pramsey
Priority: blocker Milestone: PostGIS 2.0.4
Component: postgis Version: 2.0.x
Keywords: Cc: vbalent


I have two PostGIS environments (Windows Server and Linux) My Linux Version does not retrun records and throws no errors;

I have built a simple SQL function in that 1) transforms my Lat Longs into State Plane US Feet WORKS 2) measure the distance between the two points and buffer it by 1 mile (5280) US Feet DOES NOT WORK

However No Errors are thrown, just No Records are returned.

SQL Statement



This exact same SQL statement works 100% as expected in this Version:

PostgreSQL 9.1.7, compiled by Visual C++ build 1500, 32-bit POSTGIS="1.5.5" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.6.1, 21 August 2008" LIBXML="2.7.8" USE_STATS

However not in this Version:

PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit POSTGIS="2.0.3 r11128" GEOS="3.3.

8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.6" LIBJSON="UNKNOWN" TOPOLOGY

Please advise?

Regards Val 518-457-5905

Attachments (1)

set_Linux.txt (5.3 KB) - added by vbalent 9 years ago.
Linux Path, Varaiables

Download all attachments as: .zip

Change History (13)

comment:1 Changed 9 years ago by robe


Minor suggestion, you should be using ST_DWithin(geom1,geom2,5280) instead of sT_Distance because ST_Distance will never use an index.

That aside these issues are often caused by missing nadgrid files. An easy verification:

SELECT ST_AsText(ST_Transform(ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260));

on both systems and verify they return the same answer.

comment:2 Changed 9 years ago by robe

Milestone: PostGIS 2.1.0PostGIS 2.0.4

comment:3 Changed 9 years ago by vbalent


I did check the nadgrid files and they are both reporting the exact same return values in Windows Server and Linux.

I am trying to keep this simple for now the SQL statement below, however The st_distance worked perfect from above, I know it does not utilize the Gist. But it works on Windows Server and not on Linux.

This SQL statemant errors but I can not find a Sample code where I can say =5280 feet for a Mile Radius.

SELECT * FROM PART_1_CRIMES WHERE ST_DWithin(geom, 'POINT(687884 1355669)', 2260) =5280.0;

comment:4 Changed 9 years ago by robe

You should right it like this below. It's example like you Distance <= 5280 except the distance value is part of the function.

SELECT * FROM PART_1_CRIMES WHERE ST_DWithin(geom, ST_TRANSFORM(ST_GeomFromText('POINT(-73.76545859999999 42.658618)', 4326), 2260),  5280.0) ; 

You can use ST_PointFromText as you have but ST_GeomFromText works for all geometries and is also faster speed wise. ST_PointFromText does an extra check to verify you gave it a point which is unnecessary for your use case.

BTW: this is all covered in first chapter of my upcoming 2nd edition book: and first chapter is a free download

comment:5 Changed 9 years ago by robe

I forgot to ask -- did you verify you get the same answer on both servers for this query -- I just tried my 1.5 and 2.0 installs on separate servers and got the same answer.

     ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260)

-- answer  POINT(689696.298242221 1394398.10155405)

Its possible that even though you have nadfiles your proj environment variable is not set right to read it. For windows it uses the contrib/postgis.. location if no proj environment variable is set. I don't think this is the case for Linux and I can't remember off hand what the variable is called since I haven't had to set it in a while.

comment:6 Changed 9 years ago by vbalent

Hi, I forgot to ask -- did you verify you get the same answer on both servers for this query -- I just tried my 1.5 and 2.0 installs on separate servers and got the same answer.

Yes I did check and received the exact same numbers in both environments.

I wish you could help find this proj environment variable in Linux.

On a side note when I do bump my radius from 1 mile (5280 Feet), No records, However when I bump the Radius search to like 300 miles out I DO get records returned.

Any thuoghts?


comment:7 Changed 9 years ago by vbalent

Ok i ran a command call set in Linux which gives me back all my System Setting and Variables. I did a find and did not see anything related to PostGIS

I have attached it any thoughts?

Changed 9 years ago by vbalent

Attachment: set_Linux.txt added

Linux Path, Varaiables

comment:8 Changed 9 years ago by robe

Hmm don't see a PROJ_LIB set, and I think if not set it uses the default location which might vary depending on OS. Details described here:

BUT: YOU STILL DID NOT ANSWER MY QUESTION: What does this query return?

     ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260)

Mine returns: -- answer POINT(689696.298242221 1394398.10155405)

If you get the same or similar answer then proj is not your issue and you probably have a more obvious problem of your datasets are simply different on the two systems.

If you get a different answer, then proj is your issue.

comment:9 Changed 9 years ago by vbalent

Cc: vbalent added
Resolution: worksforme
Status: newclosed


For all your help.

I finally got the Query to work.

SELECT * FROM PART_1_CRIMES where ST_DISTANCE(ST_TRANSFORM(ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260),


Was not a proj issue, just getting the syntax correct.

comment:10 Changed 9 years ago by robe

That's a little convoluted. The ST_AsText is just for displaying geometry in user friendly way not to actually use. That would be super slow with large geometries.

Now that said I see you are doing this:


Which suggests your table data is NOT in 2260 projection or if it is it is misstated but really in wgs_84 long lat (assuming your above query works as you are expecting it to).

What does:

SELECT ST_SRID(geom) from part_1_crimes limit 1;


To save yourself having to write convoluted (and super slow code that doesn't use an index) as the above, convert your table to 2260 with a command something like:

ALTER TABLE part_1_crimes ALTER TYPE geom geometry(POINT,2260) USING ST_Transform(ST_SetSRID(geom, 4326),5260);

Then you should be able to rewrite your query as follows:

 SELECT * FROM PART_1_CRIMES WHERE ST_DWithin(ST_TRANSFORM(ST_GeomFromText('POINT(-73.76545859999999 42.658618)', 4326), 2260),

    GEOM, 5280);

and if you have a gist index on your geom column, the above query should run MUCH faster and yield the same results.

comment:11 Changed 9 years ago by robe

Resolution: worksforme
Status: closedreopened

comment:12 Changed 9 years ago by robe

Resolution: invalid
Status: reopenedclosed
Note: See TracTickets for help on using tickets.