PostGIS vs. Shapefile

[Daniele Margotti]

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);




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


I hope I understood your problem well.

I don't know if you already inspected the dump of the table but you can 
use this command to see it:

pg_dump -d <database_name> --schema-only -t <table_name>

It will also show you what indexes you have on the table and how it is 
really created.

You could also try to use EXPLAIN on the SQL SELECT query if you knew 
what the query is. It would show you whether and what index is used on 
the table when the query is processed.

Miroslav Šulc



Daniele Margotti wrote:

>It didn't work: same long time...
>
>I also ran this commands (for every table):
>
>SELECT UPDATE_GEOMETRY_STATS('mytable', 'the_geom');
>
>but I didn't get a better performance.
>
>        Daniele
>
>
>-----Original Message-----
>From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] 
>On Behalf Of Bart van den Eijnden
>Sent: Thursday, February 10, 2005 1:23 PM
>To: MAPSERVER-USERS at LISTS.UMN.EDU
>Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
>
>
>Hi,
>
>see:
>
>http://postgis.refractions.net/docs/ch04.html#id3125676
>
>In short you can create the spatial index in the following way:
>
>CREATE INDEX mytable_geom_idx ON mytable USING GIST (the_geom 
>GIST_GEOMETRY_OPS);
>
>followed by:
>
>vacuum analyze;
>
>Best regards,
>Bart
>
>  
>
>>Hmm...
>>
>>I'm new to PostGIS, I imported data from shapefile to POstGIS with 
>>these
>>commands:
>>
>># su - postgres
>>$ createdb my_database
>>$ createlang plpgsql my_database
>>$ psql -d my_database -f /usr/share/pgsql/postgis.sql
>>$ psql -d my_database -f /usr/share/pgsql/spatial_ref_sys.sql
>>$ shp2pgsql /path_to_shapefiles/my_shapefile01 my_table01 | psql -d 
>>my_database
>>
>>(last command repeated for every shapefile).
>>
>>For every import, an index (I think) is created:
>>NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index 
>>'my_table01_pkey' for table 'my_table01'
>>
>>In my .map file I have:
>>#
>>DATA "the_geom from my_table01"
>>CONNECTION "user=postgres dbname=my_database host=localhost port=5432"
>>    
>>
>
>  
>
>>CONNECTIONTYPE postgis #
>>
>>
>>Are the above steps correct?
>>How can I check if I have a spatial index on my tables, and an index 
>>on the primary key of my tables?
>>
>>Thank you,
>>  Daniele
>>
>>
>>
>>-----Original Message-----
>>From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU]
>>On Behalf Of Bart van den Eijnden
>>Sent: Thursday, February 10, 2005 11:13 AM
>>To: MAPSERVER-USERS at LISTS.UMN.EDU
>>Subject: Re: [UMN_MAPSERVER-USERS] PostGIS vs. Shapefile
>>
>>
>>Hi Daniele,
>>
>>No that's not normal.
>>
>>Do you have a spatial index on your table in PostGIS? Do you have an 
>>index on the primary key of your table?
>>
>>Best regards,
>>Bart
>>
>>    
>>
>>>Hi all,
>>>I have made some spatial queries (with cgi-bin Mapserver 4.2.3) 
>>>alternately with data stored in shapefiles and PostGIS (imported from
>>>      
>>>
>
>  
>
>>>these shapefiles).
>>>
>>>My query (304 elements on 5 layers) takes 5 seconds (elaboration, and
>>>      
>>>
>
>  
>
>>>then output on browser) if data are read from shapefiles, and about 
>>>50
>>>      
>>>
>>>seconds if data are read from PostGIS.
>>>
>>>The query is the same: I simply press "reload" on my browser (after 
>>>manually changed .map file and query template files) to be sure of 
>>>it...
>>>
>>>Is it normal?
>>>
>>>    Daniele
>>>
>>>
>>>P.S.:
>>>My system is a Linux Red Hat 9.
>>>
>>>My mapserver is:
>>>$ ./mapserv -v
>>>MapServer version 4.2.3 OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP
>>>      
>>>
>
>  
>
>>>OUTPUT=PDF SUPPORTS=PROJ SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER 
>>>SUPPORTS=WMS_CLIENT SUPPORTS=WFS_CLIENT INPUT=EPPL7 INPUT=POSTGIS 
>>>INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
>>>
>>>Postgresql and PostGIS are installed from RPMs (the version is: 
>>>postgresql-postgis-7.3.4-3.rri1.i386.rpm).
>>>
>>>
>>>
>>>      
>>>