[postgis-users] Query crashed
Stephen Woodbridge
woodbri at swoodbridge.com
Sun Nov 20 17:55:45 PST 2005
I might try doing that, but I haven't had much luck making changes to
the freeBSD ports stuff so I might wait for your guys to fix it.
I sent email to Anderson <anderson at cnpm.embrapa.br> who supports
postgis on freebsd asking if he would pick up support for geos also as
Leonardo Martins <lmartins at nepe.eee.ufg.br> does not seem to be
supporting geos any more.
I was able to hack my ports directory and get geos-2.1.4 installed and
that seems to work without crashing the server.
It took 3.3 hours to process the query:
select count(*) from roadseg r, streets s where expand(r.the_geom,
0.00007) && s.the_geom and
within(s.the_geom, buffer(r.the_geom, 0.00007));
and returned 352411 as the count. Given that there are about 2 Million
rows in each table, I need to play with the 0.00007 value to see if I
can get the number higher without including false positives. 0.00007 is
about 25 ft.
-Steve
Paul Ramsey wrote:
> Looks like it got stubbed out during the hw/lw geometry conversion.
> There is code there, but it is short-circuited with a quick:
>
> elog(ERROR, "This function is unimplemented yet");
> PG_RETURN_NULL();
>
> If you are feeling funky, you can comment those lines out of the
> lwgeom_functions_basic.c file in the LWGEOM_maxdistance2d_linestring
> (PG_FUNCTION_ARGS) function and see if the code that is there actually
> works.
>
> P.
>
>
> On 20-Nov-05, at 1:22 PM, Stephen Woodbridge wrote:
>
>> Seems like the max_distance function is not implemented:
>>
>> select max_distance((select the_geom from roadseg where gid=1),
>> (select the_geom from streets where gid=1));
>>
>> ERROR: This function is unimplemented yet
>>
>> I just hacked my freebsd ports dirctory and installed geos-2.1.4 and
>> will try to run the within(a, buffer(b,0.00007)) case while I go out
>> for dinner and let you know how that does later.
>>
>> Is the join selectivitity can issue or just a notice. The query seems
>> to run anyway but I have no clue as to what it is doing?
>>
>> -Steve
>>
>>
>> Paul Ramsey wrote:
>>
>>> One last thing. The failure is due to a join selectivity
>>> calculation. If you drop the index on the table on which you are
>>> doing the expand(), perhaps PostgreSQL will skip the join
>>> selectivity calculation altogether (since only one side of the join
>>> will have an index, there is only one efficient plan).
>>
>>
>>
>>
>>> 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