[postgis-users] Help with spatial query (bug??)
strk at refractions.net
strk at refractions.net
Wed Apr 6 03:54:14 PDT 2005
Brent, can you post a simplified test of your problem so we can
check on newer geos/postgis versions ?
2 geometries and 2 queries against them should be enough AFAIKT.
--strk;
On Tue, Apr 05, 2005 at 10:38:55PM -0700, Brent Wood wrote:
>
> --- Carl Anderson <carl.anderson at vadose.org> wrote:
>
> Thanks Carl.
>
> Apologies for the length of this post, but I can't see why what I think is
> effectively the same query returns two different results.
>
>
> My problem turned out to be that a linestring with 2 points does not intersect
> with (or relate to) the polygon containing it if the startpoint = endpoint. So
> some 10000 records with 0 length lines disappeared from the join.
>
> I'm not sure if this constitutes a bug or not (I'm using v0.8 & can't change
> that easily)
>
> All the linestrings in this data subset have 2 points.
> Each of the two points is the same, so the lines are zero length.
> Each cell is a lat/long square, so the bounding box is the actual polygon.
> (In the main dataset, where non-zero length lines are present, the queries
> appear to work correctly for non-zero length linestrings)
>
> Query one:
> psql $DB -c "select t.event_key,
> c.cell_id,
> x(startpoint(t.the_geom)),
> y(startpoint(t.the_geom)),
> c.the_geom
> from chat_cells3 c,
> test_junk t
> where c.the_geom && t.the_geom;"
>
> The output shows lines on the boundary of cells, with all the cells being
> identified which touch or contain the line (point). A line(point) on the vertex
> of four neighbouring cells gets 4 records returned.
>
>
> event_key | cell_id | x | y |
> the_geom
> -----------+---------+-----------+----------+----------------------------------------------------------------------------------------
> 13683355 | 3553 | 175.5 | -44.2 | SRID=4326;POLYGON((175.45
> -44.25,175.45 -44.2,175.5 -44.2,175.5 -44.25,175.45 -44.25))
> 13683355 | 3554 | 175.5 | -44.2 | SRID=4326;POLYGON((175.45
> -44.2,175.45 -44.15,175.5 -44.15,175.5 -44.2,175.45 -44.2))
> 13683355 | 3625 | 175.5 | -44.2 | SRID=4326;POLYGON((175.5
> -44.25,175.5 -44.2,175.55 -44.2,175.55 -44.25,175.5 -44.25))
> 13683355 | 3626 | 175.5 | -44.2 | SRID=4326;POLYGON((175.5
> -44.2,175.5 -44.15,175.55 -44.15,175.55 -44.2,175.5 -44.2))
> 13677419 | 8186 | 178.68333 | -42.9833 | SRID=4326;POLYGON((178.65
> -43,178.65 -42.95,178.7 -42.95,178.7 -43,178.65 -43))
> 12583686 | 2910 | 175.01667 | -43.9833 | SRID=4326;POLYGON((175 -44,175
> -43.95,175.05 -43.95,175.05 -44,175 -44))
> 12578235 | 3198 | 175.23333 | -43.95 | SRID=4326;POLYGON((175.2
> -44,175.2 -43.95,175.25 -43.95,175.25 -44,175.2 -44))
> 12578235 | 3199 | 175.23333 | -43.95 | SRID=4326;POLYGON((175.2
> -43.95,175.2 -43.9,175.25 -43.9,175.25 -43.95,175.2 -43.95))
> 12497906 | 12481 | 181.65 | -44.2 | SRID=4326;POLYGON((181.65
> -44.25,181.65 -44.2,181.7 -44.2,181.7 -44.25,181.65 -44.25))
> 12497906 | 12482 | 181.65 | -44.2 | SRID=4326;POLYGON((181.65
> -44.2,181.65 -44.15,181.7 -44.15,181.7 -44.2,181.65 -44.2))
> 12497906 | 12409 | 181.65 | -44.2 | SRID=4326;POLYGON((181.6
> -44.25,181.6 -44.2,181.65 -44.2,181.65 -44.25,181.6 -44.25))
> 12497906 | 12410 | 181.65 | -44.2 | SRID=4326;POLYGON((181.6
> -44.2,181.6 -44.15,181.65 -44.15,181.65 -44.2,181.6 -44.2))
> 12497919 | 3270 | 175.26667 | -43.95 | SRID=4326;POLYGON((175.25
> -44,175.25 -43.95,175.3 -43.95,175.3 -44,175.25 -44))
> 12497919 | 3271 | 175.26667 | -43.95 | SRID=4326;POLYGON((175.25
> -43.95,175.25 -43.9,175.3 -43.9,175.3 -43.95,175.25 -43.95))
> 12497936 | 2841 | 174.98333 | -43.8333 | SRID=4326;POLYGON((174.95
> -43.85,174.95 -43.8,175 -43.8,175 -43.85,174.95 -43.85))
> 12497956 | 4856 | 176.38333 | -43.8666 | SRID=4326;POLYGON((176.35
> -43.9,176.35 -43.85,176.4 -43.85,176.4 -43.9,176.35 -43.9))
> (16 rows)
>
> the second query uses not disjoint, which with this data, I understand should
> give the same result. In fact only 2 records are returned. Same if I use
> intersects, relate, add in an "or touches" etc.
>
> psql $DB -c "select event_key,
> c.cell_id,
> x(startpoint(t.the_geom)),
> y(startpoint(t.the_geom)),
> c.the_geom
> from chat_cells3 c,
> test_junk t
> where c.the_geom && t.the_geom
> and not disjoint(t.the_geom, c.the_geom);"
>
>
>
> event_key | cell_id | x | y |
> the_geom
> -----------+---------+--------+-------+---------------------------------------------------------------------------------------
> 13683355 | 3626 | 175.5 | -44.2 | SRID=4326;POLYGON((175.5 -44.2,175.5
> -44.15,175.55 -44.15,175.55 -44.2,175.5 -44.2))
> 12497906 | 12482 | 181.65 | -44.2 | SRID=4326;POLYGON((181.65 -44.2,181.65
> -44.15,181.7 -44.15,181.7 -44.2,181.65 -44.2))
> (2 rows)
>
>
> I can't see why I get two different results???
>
>
>
> I guess the simple fix is to move the (or add a new) endpoint some tiny
> distance from the startpoint to create a line of non-zero length.
>
> Is there a (relatively) simple way to do this in PostGIS v0.8?
>
>
> Thanks again,
>
> Brent Wood
>
>
>
>
>
> > Brent Wood wrote:
> >
> > >I'm trying to make sense of the various spatial operators. I need to join a
> > >table of cells to a table of lines, to create a table where there is a
> > record
> > >for each case where a line passes entirely or partially through a cell.
> > >
> > >Looking at the OGC specs was a bit of help, but can anyone explain the
> > >difference between the intersects, overlaps & crosses functions:
> > >
> > >ie:
> > >
> > >select
> > >....
> > >where
> > > lines.geom && cell.geom and
> > ><one of>
> > > intersects(lines.geom, cell.geom)
> > > overlaps(lines.geom, cell.geom)
> > > crosses(lines.geom, cells.geom)
> > >
> > >I figure I should be using crosses for this query, but don't undertand the
> > >distinction between these.
> > >
> > >
> > L - references the Line geometry
> > A - references the cell, Polygon (area) geometry
> >
> > intersects (L,A) - line and polygon (cell) touch or overlay in any way
> > overlaps (L,A) - is not defined for a line vs polygon test
> > crosses (L,A) - line is part in and part out of the polygon, but not
> > wholly inside
> > within (L,A) - the line is wholly inside the polygon
> > contains (A,L) - the line is wholly inside the polygon
> >
> > you may want
> > intersects (lines.geom, cells.geom)
> > or if you want to ignore the touches only relationship
> > ( crosses (lines.geom, cells.geom) or within(lines.geom, cells.geom) )
> > or
> > ( intersects (lines.geom, cells.geom) and not touches(lines.geom,
> > cells.geom) )
> >
> >
> > C.
> >
> > >
> > >Thanks,
> > >
> > > Brent Wood
> > >_______________________________________________
> > >postgis-users mailing list
> > >postgis-users at postgis.refractions.net
> > >http://postgis.refractions.net/mailman/listinfo/postgis-users
> > >
> > >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list