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

[sgottipamula]

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)

[sgottipamula]

quantum gis view

[pramsey]

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

[robe]

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

[sgottipamula]

google earth screenshot

[sgottipamula]

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

[robe]

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.

[sgottipamula]

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.

[robe]

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.

[robe]

I mean prevent decimal truncation.

[sgottipamula]

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

[pramsey]

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.

[sgottipamula]

I've attached it now.

[pramsey]

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

[pramsey]

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?