Apache-PostgreSQL-PostGIS-PhP.md

Apache, PostgreSQL/PostGIS, PhP

misc

sudo apt-get install python-software-properties

apache

sudo apt-get update
sudo apt-get install apache2

POSTGRESQL

sudo apt-get install postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1 postgresql-server-dev-9.1

GEOS

sudo apt-get install libgeos-c1 libgeos-dev

PROJ-4

sudo apt-get install proj-bin proj-data libproj-dev

POST-GIS

sudo apt-add-repository ppa:sharpie/for-science  # To get GEOS 3.3.2
sudo apt-add-repository ppa:sharpie/postgis-nightly
sudo apt-get update
sudo apt-get install postgresql-9.1-postgis

POSTGIS template

createdb -E UTF8 template_postgis2 -U postgres
createlang -d template_postgis2 plpgsql -U postgres
psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis2'" -U postgres
psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/postgis.sql -U postgres
psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/spatial_ref_sys.sql -U postgres
psql -d template_postgis2 -f /usr/share/postgresql/9.1/contrib/postgis-2.1/rtpostgis.sql -U postgres
psql -d template_postgis2 -c "GRANT ALL ON geometry_columns TO PUBLIC;" -U postgres
psql -d template_postgis2 -c "GRANT ALL ON geography_columns TO PUBLIC;" -U postgres
psql -d template_postgis2 -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;" -U postgres
createdb training -T template_postgis2 -U postgres

Add shapefile to psql shp2pgsql -D -I -s 4326 shape/BayArea_IncorporatedCities.shp bayshp | psql -U postgres training

Additional pg config

## pg
#In /etc/postgresql/9.1/main/pg_hba.conf, set auth method to "trust":
local   all             postgres                                trust
host    all             all             127.0.0.1/32            trust

# restart psql
sudo service postgresql restart

# set pw (also add to database.yml -- optional/redundant given above??
sudo -u postgres psql
\password
(Set password) 
\q

PHP

sudo apt-get install php5 libapache2-mod-php5 php5-mcrypt

add PHP to directory index

sudo nano /etc/apache2/mods-enabled/dir.conf

<IfModule mod_dir.c>

      DirectoryIndex index.php index.html index.cgi index.pl index.php index.xhtml index.htm

</IfModule>

psql driver for php

sudo apt-get install php5-pgsql
sudo service apache2 restart

The challenge

createdb training -T template_postgis2 -U postgres
createlang plpgsql training -U postgres
# createlang: language "plpgsql" is already installed in database "spatialdb"
psql -U postgres

\c training;

CREATE TABLE coords
(
    id_key         serial primary key,
    name           VARCHAR(40) not null,
    description    TEXT,
    site_id        INTEGER not null,
    visit          BOOLEAN not null,
    created_at     DATE not null,
);

optional

training=# CREATE OR REPLACE FUNCTION public.spGeoPoly(polystring text) RETURNS setof coords
AS $$
BEGIN
RETURN QUERY
SELECT * FROM coords
WHERE ST_Contains(ST_GeomFromText('POLYGON((' || polystring || '))', 4326), the_geom);
END;
$$ LANGUAGE plpgsql;

# if ok: 
CREATE FUNCTION

# run with spGeoPoly(polystring text)
training=# SELECT spGeoPoly('0 0,0 55,55 55,55 0,0 0');

                                          spgeopoly                                          
---------------------------------------------------------------------------------------------
(38,new,newsite,345,f,2042-06-01,0101000020E610000000000000000021400000000000003440,20,8.5)
(1 row)

how to hook this in? 

SELECT spGeoPoly(SELECT ST_AsText(geom) into polystr FROM bayshp WHERE name = 'Antioch');