PostGIS vs. Shapefile

[Daniele Margotti]

x_coord and y_coord are only coordinates of the centroid - manually
added to the shapefile for certain queries using PHPMapscript.

But when I query the map using the cgi-bin Mapserver, these fields are
not considered (and they are not even in the html templates).

Anyway, I just created an index for every field of a table (except for
"the_geom", already created with option "USING GIST"): now, querying
only the layer based on that table, I don't get a better performance,
since query remains from 10 to 15 time slower than shapefile-query.

I checked the mapfile to ensure that all references to field are in
lower-case (e.g., CLASSITEM or DATA), but nothing changed.

The strange thing is that the map (/cgi-bin/mapserv?mode=browse) is
drawn at the same speed as if I would use shapefiles.

        Daniele


-----Original Message-----
From: Miroslav Šulc [mailto:miroslav.sulc at startnet.cz] 
Sent: Thursday, February 10, 2005 6:24 PM
To: Daniele Margotti
Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile


I just guess but I would try to add these indexes (I guess there are 
some coordinates that could be used for the query):

CREATE INDEX ra_strade_x_coord ON ra_strade (x_coord);
CREATE INDEX ra_strade_y_coord ON ra_strade (y_coord);
CREATE INDEX ra_strade_x_coord_y_coord ON ra_strade (x_coord, y_coord);

I would also add indexes on any field that you use as filter in your MAP

file.

Running "VACUUM ANALYZE;" after this cannot hurt :-)

But as I wrote above, I just guess, I'm no pro :-)

Miroslav Šulc


Daniele Margotti wrote:

>This is the result of the command:
>
>$ pg_dump -d ra --schema-only -t ra_strade
>
>--
>-- PostgreSQL database dump
>--
>
>\connect - postgres
>
>SET search_path = public, pg_catalog;
>
>--
>-- TOC entry 2 (OID 254941)
>-- Name: ra_strade; Type: TABLE; Schema: public; Owner: postgres
>--
>
>CREATE TABLE ra_strade (
>    gid serial NOT NULL,
>    fnode_ bigint,
>    tnode_ bigint,
>    length numeric,
>    id_arco bigint,
>    funzione integer,
>    tipo integer,
>    classe integer,
>    numero character varying,
>    liv_ini integer,
>    liv_med integer,
>    liv_fine integer,
>    nomes character varying,
>    nomed character varying,
>    type_class character varying,
>    x_coord double precision,
>    y_coord double precision,
>    the_geom geometry,
>    CONSTRAINT "$1" CHECK ((srid(the_geom) = -1)),
>    CONSTRAINT "$2" CHECK (((geometrytype(the_geom) =
>'MULTILINESTRING'::text) OR (the_geom IS NULL)))
>);
>
>
>--
>-- TOC entry 3 (OID 289905)
>-- Name: ra_strade_geom_idx; Type: INDEX; Schema: public; Owner: 
>postgres
>--
>
>CREATE INDEX ra_strade_geom_idx ON ra_strade USING gist (the_geom);
>
>
>--
>-- TOC entry 4 (OID 288321)
>-- Name: ra_strade_pkey; Type: CONSTRAINT; Schema: public; Owner: 
>postgres
>--
>
>ALTER TABLE ONLY ra_strade
>    ADD CONSTRAINT ra_strade_pkey PRIMARY KEY (gid);
>
>  
>