Opened 12 years ago

Closed 11 years ago

#2162 closed defect (fixed)

ST_Intersects yields null where I see a clear intersection on quantum gis

Reported by: sgottipamula Owned by: pramsey
Priority: medium Milestone: PostGIS 1.5.9
Component: postgis Version: 1.5.X
Keywords: Cc:

Description

I have a base_table with a bunch of geographies and each geography has an attributes associated with it.

I have a reference table which just contains a single point geography (lat/long)

When I render these two on quantum gis I clearly see the point inside a layer along with its attributes. However, when I try to use the ST_Intersects function in postgis on these geographies I don't get anything back. This query works for most of our lat-longs but I have a handful of cases where this behavior is observed. I am not sure why this is happening?

I have attached the quantum gis screenshot for the two geographies and below is the query that I used:

SELECT value from base_table bt, san_ref_point srp where ST_Intersects(bt.geog, srp.geog)

If it helps these are the coordinates for the point:(-122.15023 37.905731)

Attachments (3)

ScreenClip.png (57.2 KB ) - added by sgottipamula 12 years ago.
quantum gis view
Screen Shot 2013-01-20 at 6.22.10 PM.png (894.6 KB ) - added by sgottipamula 12 years ago.
google earth screenshot
insert.sql (48.8 KB ) - added by sgottipamula 11 years ago.

Download all attachments as: .zip

Change History (16)

by sgottipamula, 12 years ago

Attachment: ScreenClip.png added

quantum gis view

comment:1 by pramsey, 12 years ago

Within the data itself, this ticket isn't actionable.

comment:2 by robe, 12 years ago

I think QGIS always displays in planar. Have you verified you are not suffering from this common misunderstanding.

http://blog.opengeo.org/2012/04/30/the-earth-is-not-flat-volume-2

by sgottipamula, 12 years ago

google earth screenshot

comment:3 by sgottipamula, 12 years ago

Even when I convert it to kml and visualize it on google earth I see a clear intersection. Please see the new attachment.

comment:4 by robe, 12 years ago

sgottipamula — do you have 2 geographies you can output in sql we can work with. Google earth doesn't quite follow spherical. They use some tricks along the way so is still not proof there is an issue. This is not to say there isn't but a picture isn't very actionalble anyway.

We'll need a real set of geographies to work with to troubleshoot. If its personal data, you can send to pramsey off list

pramsey at cleverelephant.ca

or me (lr at pcorp.us) or both of us. pramsey would be better able to debug since he wrote a good chunk of the geography plumbing.

comment:5 by sgottipamula, 12 years ago

I was actually in process of attaching the script to create a dummy table with just that geography record for easy repro. Interestingly, the query works fine on this table with just one record.

Does the number of records in the original table (83924) have anything to do with this anomaly? All I did was use st_astext(geog) to extract that particular record from base table and use a insert script with st_geographyfromtext()

I've sent you both the script to create this geography if that helps.

comment:6 by robe, 12 years ago

In theory it shouldn't though we had a bug a while back that only showed if one record. I think more likes is the ST_AsText use. ST_AsText doesn't go past I think 14 significant decimals so is not a true representation of your point.

Try outputting with geog::text that will give the hex encoded binary representation which will present decimal truncation.

comment:7 by robe, 12 years ago

I mean prevent decimal truncation.

comment:8 by sgottipamula, 12 years ago

That was it. Thanks. I am able to repro it on the dummy table now. I've sent over the updated script.

comment:9 by pramsey, 11 years ago

There's no data attached to this ticket, so I can't do anything about it. The detail that it "works if the table has one row, but not if too" indicates a difference between the brute-force calculation (one row) and the cached-tree calculation (more than one row) so I can believe it's a real bug, but without the exact data to reproduce it, I cannot do anything.

by sgottipamula, 11 years ago

Attachment: insert.sql added

comment:10 by sgottipamula, 11 years ago

I've attached it now.

comment:11 by pramsey, 11 years ago

Hm, could you now include some SQL that demonstrates the failure condition? The SQL at the top of the file returns an intersection, is that correct or not? ST_Distance (which calls the same code, ultimately, as ST_Intersects) also returns zero distance, and in both the tree and non-tree modes:

postgis21=# SELECT _ST_DistanceTree(bg.geog, ST_GeogFromText('SRID=4326;POINT(-122.15023 37.905731)')) from bug_repro bg;
 _st_distancetree 
------------------
                0
(1 row)

postgis21=# SELECT ST_Distance(bg.geog, ST_GeogFromText('SRID=4326;POINT(-122.15023 37.905731)')) from bug_repro bg;
 st_distance 
-------------
           0

comment:12 by pramsey, 11 years ago

I'm not seeing this problem in the latest 1.5 version

postgis15=# SELECT value from bug_repro bg where ST_Intersects(bg.geog, ST_GeogFromText('SRID=4326;POINT(-122.15023 37.905731)'));
 value 
-------
     3
(1 row)

Is it possible a micro update to the latest 1.5 could fix you?

comment:13 by sgottipamula, 11 years ago

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.