[postgis-users] I posted some mapserver and postgis debs

[Kevin Murphy]

Silke Reimer wrote:

>On Wed, Sep 22, 2004 at 09:37:44PM -0400, Stephen Frost wrote:
>  
>
>>* Fred McDavid (frm at bitdaddy.com) wrote:
>>    
>>
>What prevented you to do so? I would be very interested in having
>postgis in debian. There has been an ITP for postgis in 2002 but
>since now we still haven't postgis in debian
>  
>

As a debian 'testing' user, I could aid with testing and cheerleading 
(although I haven't tested the recently mentioned packages yet :-( ).

Just for the record, I am very pleased with postgis.  I know knothing 
about GIS programming, but in just 4 hours or so I was able to prototype 
a web application to show maps of a user-selected US county and its 
adjacent counties (using Perl, CGI.pm, DBI.pm, SVG.pm, and 
batik-rasterizer).  16 or so hours later it's still beta, but none of 
the problems have been on the database side; they're all SVG-related.

My current challenge is that I have belatedly noticed that some counties 
have multiple polygons (which are stored as separate records).  There 
are two issues with this: 1) is there a single query I can use to find 
the neighboring counties of the central county, and 2) I'm automatically 
centering county labels on the centroids of the polygons, but what to do 
now with the multi-polygons?  I feel confident I can find the answer to 
these questions myself, but I just thought I'd throw them out here since 
I'm writing.  I also now need to do proper map coloring, and I haven't 
found a Perl module to do this, so in the next few weeks I am hoping to 
add coloring algorithms to the CPAN Graph modules.

Also just for the record, here's how I built my mapping app using Debian 
Linux 'testing' (Sarge):

1.  Install the software: PostgreSQL, libgeos (GEOS), libproj (PROJ4), and
libpostgis (postgis).  This is non-trivial but wasn't too bad for me using
Debian Linux (testing/sarge).  I used the stock 'postgres' and 'proj'
packages and installed GEOS and postgis from source.  My starting point for
all this was:
http://edseek.com/archives/2004/03/31/installing-postgis-on-debian-gnulinux/,
except that the replacement path he provided in the sed command did not work
for me.  I had to use /usr/lib/postgresql/lib, or something like that (do
'sudo updatedb' and then 'locate libpostgis', or look at the makefile for
postgis to see where it installed its libraries).  When installing the GEOS
library from source, do not forget to modify /etc/ld.so.conf, add
'/usr/local/lib', and run 'sudo ldconfig'.  See also
http://postgis.refractions.net/.  Postgis is at the top of the foodchain,
i.e. it uses the GEOS and Proj4 libraries to do its business.

2. Make sure to create the database you want to use within PostgreSQL.  To
work conveniently as yourself, add yourself as a database user first:

sudo su - postgres
createuser your_unix_account_name
exit
createdb geo
createlang plpgsql geo

2b. Also add a user for the webserver user.  I'm not sure if it was 
stock debian, but the webserver user and group I had been using was 
www-data, but I changed this to www (in /etc/passwd, /etc/group, and 
/etc/apache-perl/httpd.conf), because postgresql didn't seem to like the 
user name 'www-data'.
createuser www

3. Download US county boundary data here:

http://edcftp.cr.usgs.gov/pub/data/nationalatlas/countyp020.tar.gz

4. Convert the shapefile format into a runnable SQL script to do the table
creation and inserts:

/usr/lib/postgresql/bin/shp2pgsql countyp020.shp county_boundaries 
 >create_county_boundaries.sql

(county_boundaries is the name of the table to be created)

5. Import the data:

psql -d geo -f create_county_boundaries.sql

6. Create desired indexes on the new table:

psql -d geo -f create_indexes.sql

where you created create_indexes.sql to suit yourself.   Mine looks like 
the following, for this application:

-- Add the upper-case-mapped county column if not already done
ALTER TABLE county_boundaries ADD COLUMN county_upper VARCHAR(100);
UPDATE county_boundaries SET county_upper = UPPER(county);

-- Create an index to speed state queries
DROP INDEX county_boundaries_state_idx;
CREATE INDEX county_boundaries_state_idx ON county_boundaries(state);

-- Create an index on the up-cased county names
DROP INDEX county_boundaries_county_upper_idx;
CREATE INDEX county_boundaries_county_upper_idx ON 
county_boundaries(county_upper);

-- Most import index of all: the index on the polygons
DROP INDEX county_boundaries_the_geom_idx;
CREATE INDEX county_boundaries_the_geom_idx ON county_boundaries USING 
GIST ( the_geom );

-- Make sure changes are reflected in the optimizer statistics
VACUUM ANALYZE;
SELECT UPDATE_GEOMETRY_STATS();

-- Create a group to control access to the geo database (if not already 
created)
CREATE GROUP geo_users;

-- Add permissions on this table to this group
GRANT SELECT ON county_boundaries TO GROUP geo_users;

-- Make sure the webserver has read-access on the tables
ALTER GROUP geo_users ADD USER www;


7. Write the application.   In my app, there is one (defective) geo 
database query:

SELECT county, state, AsSVG(the_geom), Centroid(the_geom)
   FROM county_boundaries
   WHERE county = ? AND state = ?
UNION
SELECT county, state, AsSVG(the_geom), Centroid(the_geom)
FROM
(SELECT county, state, the_geom
FROM county_boundaries
WHERE the_geom && (SELECT the_geom
                   FROM county_boundaries
                   WHERE county = ? AND state = ?)) AS bb
WHERE Touches(the_geom, (SELECT the_geom
                   FROM county_boundaries
                   WHERE county = ? AND state = ?))

I'm not a SQL or postgis whiz, so this may be less than wonderful code.  
My intent was to do a fast bounding-box query using the && operator and 
then do the more expensive Touches() operation within those results.

Also note that this query is completely wrong for US counties as 
imported from countyp020.shp, because it blows up for counties with 
multiple polygons.  I am in the process of "unrolling" the query into 
separate queries and loops for each polygon of the central county.

8.  If you are displaying large areas or working professionally, you 
will want to get into projections, which I haven't yet.  But I trust 
that it will be easy with postgis/GEOS/PROJ4.

Thanks to everyone who has helped provide these great tools.

Kevin Murphy