PostGIS vs. Shapefile

[Daniele Margotti]

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