[postgis-users] Re: [postgis-users] Postgis too slow

[shpr@libero.it]

ok, I don't still try if this is the problem, but I think so.

If I develop, for example, a java servlet that extract the oid (is 
possible? I think so) and then I create a filter query only on this 
attribute, do you think that this can work faster?

Stefano.


> OK, I am now pretty sure the problem was (2) in my previous email. You
r
> mapfile layer definition is below. What you are doing is emulating an
> item query by using the FILTER parameter. This is *bad*. It will *not*
> work, because the spatial parameter is way too big, and your attribute
> indexes will never ever get used. The pgsql query optimizer just does
> not know how to handle this combination of spatial and attribute
> indexes. 
> 
> To verify that this is the problem, either (1) change your EXTENT so
> that it is a very *small* box which contains just your one test road a
nd
> very little else. Now see how fast it runs. Or (2) remove the FILTER
> entirely (don't to 1=2, *remove* it) and zoom into a small area. Now p
an
> around the dataset. That should demonstrate the native spatial query
> speed.
> 
> You are not going to be able to do this query with the standard CGI
> interface. You are going to have to use PHP MapScript and use PHP dire
ct
> connection to pgsql to do the attribute query and find the extent of t
he
> resultant lines, then set the extent of your map appropriately, then
> render. If you have simple queries, you can use the itemquery in the C
GI
> interface, but your query is too complex for that.
> 
> This is map postgis layer:
> 
> LAYER
> STATUS OFF
>   NAME "stradeQ"
> CONNECTIONTYPE postgis
> CONNECTION "user=postgres password=postgres dbname=market host=server
> port=5432"
>   DATA "the_geom from streets"
> TYPE LINE
> FILTER "(upper(st_nm_base) Like upper('%street%')) AND (l_area_id Like
> '%value%')
> AND ('%number%'='' or '%number%' between l_refaddr and l_nrefaddr or
> '%number%'
> between r_refaddr and r_nrefaddr) "
> 
> CLASS
>   SYMBOL 0
>   OUTLINECOLOR 25 25 0
> END
> TEMPLATE market.html
> END
> 
> 
> -- 
>       __
>      /
>      | Paul Ramsey
>      | Refractions Research
>      | Email: pramsey at refractions.net
>      | Phone: (250) 885-0632
>      \_
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>