[postgis-users] RE: [mapserver-users] Street Searching

Wed May 15 06:58:52 PDT 2002

Since you are doing a where clause on street etc. - it would help to index
those fields as a set - I don't think this query even really relies that
much of the spatial index since you are not doing any searches against the
spatial field.  Whatever fields you use most commonly in the filter part
should be indexed together.

I'm also curious - how many records do you have in your dataset
how fast is the server that your postgis is running on?
Which version of postgres are you using?

Also in your postgresql.conf file - you should up the memory for sorting
(depends on how much RAM you have to spare) - that helped my query speeds
significantly.  The default config is really tiny.

You could also try doing the EXPLAIN clause in postgres - don't remember off
the top of my head how you do that then you will see the plans it is using
the indexes it is using if any etc.

-----Original Message-----
From: Stefano Bonnin [mailto:shpr at libero.it]
Sent: Wednesday, May 15, 2002 9:37 AM
To: Obe, Regina DND\MIS
Subject: Re: [mapserver-users] Street Searching

I tried your postgis solution, it works!

... but the postgis query is too slow............

I tried to create a spatial index (GIST) but the query is always slow....
(3,4 minutes!!!!)

Why?

Thanks.

----- Original Message -----
From: "Obe, Regina DND\MIS" <robe.dnd at ci.boston.ma.us>
To: "'Stefano Bonnin'" <shpr at libero.it>
Sent: Tuesday, April 30, 2002 2:42 PM
Subject: RE: [mapserver-users] Street Searching

> Okay - here it is below (I just reduced it down to its main parts since
the
> map file I have is kind of long so hard to follow otherwise and so is my
> template).
>
> ----MAP FILE QUERY LAYER (Postgis version)---
> LAYER
> STATUS OFF
>   NAME "parcelsq"
> CONNECTIONTYPE postgis
> CONNECTION "user=robedev dbname=dnddts host=dnd1"
>   DATA "the_geom from streets"
> TYPE LINE
> FILTER "upper(street) Like upper('%Street%%') AND zip LIKE '%zip%%'
> AND ('%StNum%'='' or '%StNum%' between fromleft and toleft or '%StNum%'
> between fromright and toright) "
> CLASS
>   SYMBOL 0
>   OUTLINECOLOR 25 25 0
> END
> HEADER parcelfinder.htm
> TEMPLATE blank.htm
> FOOTER parcelfooter.htm
> END
> ---------------------------------------------
> The search form portion of my template (parcelfinder.htm template)
>
> <form action="/mapserv/scripts/mapserv35.exe" method=get>
> <B>Query By Address</B><BR>
> Street Num: <input type="text" name="StNum" size=4> Street: <input
> type="text" name="Street" size=15> <B>Zip:</B> <input type=text name=zip
> size=5 maxlength=5> <input type=submit name="cmdSearch" value="Search">
> <input type=hidden name=qlayer value="parcelsq">
> <input type=hidden name=template value="parcelfinder.htm">
> <input type=hidden name=map value="[map]">
> <input type=hidden name=mode value="itemnquery">
> <input type=hidden name=layer value="parcelsq">
> <input type="hidden" name="mapext" value="shapes">
> </form>
>
> (This centers the map to show the extent of all streets that match and if
I
> had a template (blank.htm with the field names - it would list each street
> segment), but I think if you just wanted to return the first match, you
> would use itemquery mode instead of itemnquery mode).
>
> I did a similar thing with ASP/and mapserv cgi - I think similar to what
> others mentioned but instead of lat/long I did an Access database search
on
> a table that had unique ids for each record (which I had in my shape file)
> and returned the primary key of the first matching record and then used
that
> in an expression. (similar SQL where search - but then redirects to
mapserv
> page passing along the id) - the filter part of that is then simply
>
> FILTERITEM "gid"
> FILTER "%gid%"
>
> where gid is the primary key in my streets table.
>
>
>
>
> -----Original Message-----
> From: Stefano Bonnin [mailto:shpr at libero.it]
> Sent: Tuesday, April 30, 2002 4:23 AM
> To: Obe, Regina DND\MIS
> Subject: Re: [mapserver-users] Street Searching
>
>
> oh yes, I am very interested!
>
> Thanks!
>
> ----- Original Message -----
> From: "Obe, Regina DND\MIS" <robe.dnd at ci.boston.ma.us>
> To: "'Stefano Bonnin'" <shpr at libero.it>;
<mapserver-users at lists.gis.umn.edu>
> Sent: Friday, April 26, 2002 6:17 PM
> Subject: RE: [mapserver-users] Street Searching
>
>
> > I've done a similar thing using postgis and just the CGI mapserv, but I
> > think you can also achieve a similar result using shape files (and
regular
> > expressions - which I'm not too familiar with using) and CGI.  I can
send
> > you one of my map files and htm template if you are interested in
looking
> at
> > that.
> >
> > -----Original Message-----
> > From: Stefano Bonnin [mailto:shpr at libero.it]
> > Sent: Friday, April 26, 2002 7:02 AM
> > To: mapserver-users at lists.gis.umn.edu
> > Subject: [mapserver-users] Street Searching
> >
> >
> > Hi,
> >
> > I have to develop a Street Searching function where
> >
> > the user can insert City and Street ( and number) and the application
must
> > display the map centered on this street.
> >
> > How can I do that with mapserver?
> > Can you give me an Hint in order to do this.
> >
> > I read the PHP Map/documentation but I did't find any query of this
type.
> > My personal solution is the following:
> >
> > with a PHP application I get the user input (Street and City) and I
query
> to
> > the database in order to find the object ID, then, I "say" to mapserver
to
> > create a map with this object ....
> >
> > Someone can say me if this is a possible solution or if there are a more
> > simple solution?
> >
> > Thanks.
> >
> > Stefano B.
> >
> >
> > ---
> > Outgoing mail is certified Virus Free. (FLASHH!)
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.350 / Virus Database: 196 - Release Date: 17/04/02
>
>
> ---
> Outgoing mail is certified Virus Free. (FLASHH!)
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.351 / Virus Database: 197 - Release Date: 19/04/02

---
Outgoing mail is certified Virus Free. (FLASHH!)
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.361 / Virus Database: 199 - Release Date: 07/05/02