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
## 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
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,
);
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');