--
MonishaKanoth - 18 May 2025
Table of contents
Using POSTGIS Extension with PostgreSQL?
To Create Column and Store Geospatial Information
To allow GIS objects to be stored in the database, create a table to hold geospatial information and add geometry column:
Example:
To hold point data-
SELECT addGeometryColumn('dbname','tablename','the_geom',-1,'POINT',2);
UPDATE
set the_geom =makePoint(longitude,latitude);
For more detailed info about using POSTGIS go to: POSTGIS Manual
Shapefiles
pgsql2shp dumper converts the table or columns to shapefiles.
On Trident:
Example:
/usr/local/pgsql/bin/pgsql2shp -u postgres -h db_host_name.baruch.sc.edu -f location 'SELECT * FROM table_name';
shp2pgsql loader converts shapefiles to sql that can be loaded into postgres.
On Trident:
*Example:*
/usr/local/pgsql/bin/shp2pgsql location.shp new_table_name > location.sql
/usr/local/pgsql/bin/psql -U postgres -h db_host_name db_name -f location.sql
PostgreSQL? Notes
pgsql 'slash' commands
\a toggle between unaligned and aligned output mode
\f [STRING] show or set field separator for unaligned query output. eg. \f , (or) \f |
\o [FILE] send all query results to file or |pipe
RULES
SQL to view rules that currently exist on your tables/views: SELECT * FROM pg_rules;
Create Rules
Examples:
CREATE RULE update_geom_multi_obs_carocoops AS ON INSERT TO multi_obs DO UPDATE multi_obs SET the_geom =makePoint(m_lon,m_lat);
CREATE RULE update_test_carocoops AS ON INSERT TO multi_obs DO UPDATE test SET latest_m_value =new.m_value WHERE sensor_id=new.sensor_id;
Drop Rules
DROP RULE update_test_carocoops ON multi_obs;
Links for Rules Help:
Imagemap mouseover on portal_rs
Scripts
- map_session.php - for each layer calls mk_imagemap2.php
- mk_imagemap2.php - uses .map file,and template html file to create html for mouseover
- mk_imagemap.pl - returns tmp file with imagemap html
- mergemaps.pl - for multiple layers selected creates one html files from multiple imagemap html files
- cleanmaps.pl - final clean up of imagemap file to remove tags.
to top