PostGIS vs. Shapefile

[Daniele Margotti]

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