Opened 11 years ago

Closed 11 years ago

Last modified 11 years ago

#2369 closed defect (invalid)

SQL Statemant Works in Windows version 1.5x Not in Liunx version 2.0x

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


This is the second time this week that my ST Functions that worked fine in Windows Server are now not working in Linux.

I truly doubt PostGIS is trying to break a lot of peoples codes?

SQL Statement

SELECT P1.* FROM PART_1_CRIMES P1, "PatrolZones" PZ WHERE P1.PART_I_CRIME IN ('MURDER','RAPE','ROBBERY','AGGRAVATE D_ASSAULT','BURGLARY','LARCENY','MV_THEFT','MV_LARCENY') AND PZ.ID_AS_STRI IN('101','102','103','104','105','106', '107','108','109','110','201','202','203','204','205','206','207','208','209') AND ST_WITHIN(P1.GEOM,PZ.the_geom) ORDER BY P1.BEGINNING_DATE;

This exact same SQL statement works 100% as expected in this Windows 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 Linux 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?

Thanks, Val

Attachments (2)

WindowsPostGIS_Works.bmp (1.5 MB ) - added by vbalent 11 years ago.
LinuxPostGIS_not_Working.rtf (2.4 KB ) - added by vbalent 11 years ago.

Download all attachments as: .zip

Change History (9)

comment:1 by Bborie Park, 11 years ago

Milestone: PostGIS 2.1.0PostGIS 2.0.4
Priority: criticalhigh

What's the projection of P1.GEOM and PZ.the_geom? And are you sure that the geometries are in the projection indicated by the SRID? Your last ticket showed that the geometries were not in the projection of the recorded SRID.

comment:2 by vbalent, 11 years ago

Hi yes.

Thanks you for helping the last time on my previous ticket and I modified the changes to utilize the Gist Index on the Geom.

Currently my points that I am looking to find in polygons are both in the Same SRID

Ran the following select UpdateGeometrySRID('public', 'PatrolZones', 'the_geom', 2260) ;

Part_1_Crimes are already in 2260

psm_gee=# SELECT ST_SRID(the_geom) from "PatrolZones" limit 1;



(1 row)

psm_gee=# SELECT ST_SRID(geom) from part_1_crimes limit 1;



(1 row)

by vbalent, 11 years ago

Attachment: WindowsPostGIS_Works.bmp added

by vbalent, 11 years ago

comment:3 by vbalent, 11 years ago

I have attached 2 screenshots, one from Windows and one form Linux.

Windows Works, Linux does not. I actually would have expected the opposite in my honest opinion.

Same Function call, same SQL Syntax.

Very Baffling.

comment:4 by Bborie Park, 11 years ago

Have you checked the output of ST_AsEWKT(geom)? Just because the SRID is 2260, doesn't mean the coordinates are.

According to, coordinates for 2260 should be bounded between 116071.8523, 747947.1638, 837761.3518, 2260847.6417. If your coordinates aren't in those bounds, you have a mismatch between what the coordinates truly are and what projection someone says they are.

comment:5 by robe, 11 years ago

Resolution: invalid
Status: newclosed

vbalent based on the last ticket you posted, I suggested you do the below query because given the convoluted query you gave I had already concluded your data is stamped as 2260 but is really in wgs 84 long lat (4326) as Bborie also mentioned above.

Sorry I had a typo in the last ticket could I gave. I apologize for the confusion there. You should run this

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

I'm marking this as invalid again because your issue seems to not be a bug but just a newbie misunderstanding about using PostGIS. Please subscribe to our user's list and post your questions there before assuming they are bugs in the software.

comment:6 by robe, 11 years ago

hmm I should probably try running code before I post it. here is yet another typo correction.

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

comment:7 by vbalent, 11 years ago


Yes I checked the 2 different tables I am using.

I used the Function ST_AsEWKT(geom)

My polygon Table is SRID 2260 with True Us East State Plane Coordinates

My points table is SRID 2260 with Lat Long Values.

I will run the command you sent so that they are both in the Same Geometry not just said SRID.

I hope this command works and not break anything on my other apps. I understand they should all be in the same geometry everything.

My point and why I said it might be a defect is that It worked in Windows 64 bit, but now it did not work in Linux 64 bit.

Thanks, Val

Note: See TracTickets for help on using tickets.