Getting Started with OpenShift Spatial (Part 2)

Now let's remotely login into OpenShift using the ssh host name revealed by show-domain, and then enable postgis extensions.

rhc show-domain
ssh host
createlang -dspatialapp plpgsql
psql -dspatialapp -f /usr/share/pgsql/contrib/postgis-64.sql
psql -dspatialapp -f /usr/share/pgsql/contrib/spatial_ref_sys.sql
psql -dspatialapp

We should now be at the psql prompt. Let's create a new spatial table to store point events.

spatialapp=#
CREATE TABLE eventpoints
(
 gid serial NOT NULL,
 name text, -- name of the point
 type text, -- event type
 the_geom geometry,
 CONSTRAINT eventpoints_pk PRIMARY KEY (gid ),
 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
WITH (
 OIDS=FALSE
);

CREATE INDEX eventpoints_spatial_idx ON eventpoints USING gist ( the_geom );

Finally let's insert our first record and then view it to confirm the entry.

Insert into eventpoints (name, type, the_geom) VALUES ('First point!', 'FIRE', ST_GeomFromText('POINT(-85.7302 37.5332)', 4326));
select * from eventpoints; 
gid |    name     | type |                      the_geom                      

-----+-------------+------+----------------------------------------------------

  1 | First point! | FIRE | 0101000020E610000082E2C798BB6E55C0151DC9E53FC44240
(1 row)