[postgis-users] Query crashed
Paul Ramsey
pramsey at refractions.net
Sun Nov 20 12:43:54 PST 2005
I take it back, 100% vertical and 100% horizontal lines would also
fail. Oh well, just have to fix things... :)
P
On 20-Nov-05, at 12:40 PM, Paul Ramsey wrote:
> Oh, one other thing, unless there is something wrong, things which
> are "almost the same" should have bounding boxes that interact with
> a tolerance level of 0. So you should be able to do the query
> without the expand() at all, and still get the same results. Not
> that this is the fix for the bug, but it should at least be fast
> and effective :) (I think the only features for which this would
> fail would be shorter than your tolerance number.)
>
> select count(*)
> from
> roadseg r, streets s
> where
> r.the_geom && s.the_geom and
> max_distance(s.the_geom, r.the_geom) < 0.00007;
>
> Paul
>
>
> On 19-Nov-05, at 9:39 PM, Paul Ramsey wrote:
>
>> We'll wait until Mark gets back on the list and comments, probably
>> a problem with the function(geometry) && geometry construction and
>> the index selectivity calculation. Mark was working on spatial
>> join selectivity a couple months ago, but this might be a case he
>> did not anticipate.
>>
>> P
>>
>> On 19-Nov-05, at 4:38 PM, Stephen Woodbridge wrote:
>>
>>> Paul,
>>>
>>> Thank you for the help and suggestions. max_distance() sounds
>>> like a good way around the crash problem, but I get ...
>>>
>>> canada=# select count(*)
>>> canada-# from
>>> canada-# roadseg r, streets s
>>> canada-# where
>>> canada-# expand(r.the_geom, 0.00007) && s.the_geom and
>>> canada-# max_distance(s.the_geom, r.the_geom) < 0.00007;
>>> NOTICE: LWGEOM_gist_joinsel called with arguments that are not
>>> column references
>>> ERROR: This function is unimplemented yet
>>>
>>> I have the function defined in my database as:
>>>
>>> CREATE OR REPLACE FUNCTION max_distance(geometry, geometry)
>>> RETURNS float8 AS
>>> '/usr/local/lib/liblwgeom.so.1', 'LWGEOM_maxdistance2d_linestring'
>>> LANGUAGE 'c' IMMUTABLE STRICT;
>>> ALTER FUNCTION max_distance(geometry, geometry) OWNER TO pgsql;
>>>
>>> -Steve
>>>
>>> Paul Ramsey wrote:
>>>> Just realized, there is a max_distance() function in PostGIS, so
>>>> you can make replace the within(buffer()) call with something
>>>> much faster that is almost semantically equivalent:
>>>> select count(*)
>>>> from
>>>> roadseg r, streets s
>>>> where
>>>> expand(r.the_geom, 0.00007) && s.the_geom and
>>>> max_distance(s.the_geom, r.the_geom) < 0.00007;
>>>> The semantics of max_distance are here:
>>>> <http://postgis.refractions.net/pipermail/postgis-users/2002-
>>>> January/ 000554.html>
>>>> P.
>>>> On 19-Nov-05, at 2:29 PM, Paul Ramsey wrote:
>>>>> Well, you crashed the backend... probably with the buffer(),
>>>>> though the gist_joinsel error is oddball. Oh, your GEOS is
>>>>> quite over a year old and a lot of bugs have been scrubbed
>>>>> since then, so an upgrade there would help in general.
>>>>>
>>>>> Finally, your query syntax is hell, here is why:
>>>>>
>>>>> buffer(foo) && somethingelse
>>>>> you are doing an incredibly expensive geometry operation
>>>>> (buffer ()) and then only comparing the bounding box! use
>>>>> expand() instead
>>>>>
>>>>> within(something,buffer(foo))
>>>>> well, I see what you are doing here, and there is currently
>>>>> no other way. you are looking for items which are "mostly
>>>>> equal" to other items. that is, they may have had some
>>>>> coordinate drift do to rounding errors, or been noded in
>>>>> places, or had some vertices added or removed, but they
>>>>> basically describe the same line on the ground. what is
>>>>> really needed is a kindaequals (geom_a,geom_b,tolerance)
>>>>> function that returns a number of values, depending on the
>>>>> equality case: 1 = completely equal, 2 = describe the same
>>>>> line within tolerance, 3 = geom_a is same as geom_b but
>>>>> shorter, 4 = geom_b is same as geom_a but shorter.
>>>>>
>>>>> P.
>>>>>
>>>>> On 19-Nov-05, at 1:56 PM, Stephen Woodbridge wrote:
>>>>>
>>>>>> Hi all,
>>>>>>
>>>>>> I was running the following query:
>>>>>>
>>>>>> select count(*)
>>>>>> from
>>>>>> roadseg r, streets s
>>>>>> where
>>>>>> buffer(r.the_geom, 0.00007) && s.the_geom and
>>>>>> within(s.the_geom, buffer(r.the_geom, 0.00007));
>>>>>>
>>>>>> And it crashed with the following error message after running
>>>>>> for 1,959,844 ms in pgadmin3:
>>>>>>
>>>>>> NOTICE: LWGEOM_gist_joinsel called with arguments that are
>>>>>> not column references
>>>>>> server closed the connection unexpectedly
>>>>>> This probably means the server terminated abnormally
>>>>>> before or while processing the request.
>>>>>>
>>>>>> The "roadseg" table is the geobase roadseg data for all
>>>>>> provinces loaded into a table and the "streets" is the same
>>>>>> for the Census streets data for all provinces.
>>>>>>
>>>>>> postgis_full_version() reports:
>>>>>> POSTGIS="1.0.4"
>>>>>> GEOS="2.0.1"
>>>>>> PROJ="Rel. 4.4.9, 29 Oct 2004"
>>>>>> USE_STATS DBPROC="0.3.0"
>>>>>> RELPROC="0.3.0"
>>>>>>
>>>>>> and version() reports:
>>>>>>
>>>>>> "PostgreSQL 8.0.4 on i386-portbld-freebsd5.3, compiled by GCC
>>>>>> cc (GCC) 3.4.2 [FreeBSD] 20040728"
>>>>>>
>>>>>> Any ideas on what this is about. All the data was loaded via
>>>>>> shp2pgsql.
>>>>>>
>>>>>> Thanks,
>>>>>> -Steve
>>>>>> _______________________________________________
>>>>>> 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
>>>
>>> _______________________________________________
>>> 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