[postgis-users] RE: [postgis-users] Postgis too slow
shpr@libero.it
shpr at libero.it
Thu May 23 08:16:10 PDT 2002
I tried to change from line to query and the big advantage that I
notice is the following:
if I write a filter of this tipo
filter "oid = '%number'"
the query is evaluated immediately!!
This solution give me a possibility to write a Java Servlet or ASP
application which extract the right oid from postgres and then redirect
the query to mapserver. I believe that this solution should be possible!
Thanks
> I find all this pretty fascinating and so far educational. I didn't r
ealize
> how the mapserver and postgis integration works. I have a similar que
ry
> setup using CGI and the speed is pretty decent (under a second local)
(but
> the table I use generally for street segment only has 40,000 street se
gments
> and my memory and processor are a bit better than Stefano's.
>
> One thing I was wondering about - and whether anyone has tried this.
> Stefano says he has a 600 Mhz processor with 192mb of ram and an 800,0
00
> record set. I'm wondering if anyone has actually tried doing what he
is
> doing using 192mb of memory with that many records. I told him I can
barely
> get my server to work with all the services it has to run for that lit
tle
> amount of on-board memory. All my servers have at least 256MB ram -
even
> the really stupid ones that do nothing.
>
> The other curiosity I had is that if you set the Layer type to QUERY i
nstead
> of LINE does it still incur the same spatial query penalty - or would
> mapserver-
postgis be smart enough to say ("I'm not going to render this so
> just need the extent") and further if the map mode was set to itemquer
y
> instead of itemnquery -
if that minimizes on spatial query penalty since it
> knows to only grab first result.
>
>
>
>
> -----Original Message-----
> From: Paul Ramsey [mailto:pramsey at refractions.net]
> Sent: Wednesday, May 22, 2002 7:09 PM
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] Postgis too slow
>
>
> 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
>
> _______________________________________________
> 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