It seems very nice for the new PostGres8.3 installer to bundle up third party libraries installation with the new admin pack feature. Using this feature, you can install PostGis, nPgsql, etc. The installation was painless and smooth this time. I have to backup all my data though using pg_dump.
To lessen the learning curve on installing PostGres + PostGis together, I made a short flash movie here. There is no audio in the tutorial. The idea was to introduce PostGres to my Chinese staff. I have to remove PostGreSQL 8.2 first before installing 8.3.
I have an internet/web application sitting on top of OpenLayers, ExtJS, ColdFusion and PostGreSQL/PostGIS. I can query the geometry from PostGIS thru ColdFusion without any problems. If you are in ColdFusion 7, then you need a postgresql driver installed, but I do suggest you use ColdFusion 8 as the driver is built-in.
To display the geometry, I typically use AsGML output from PostGIS and supply that path to OpenLayers. Although it works as expected, I need to invest in two (2) http calls to the server:
- 1st Request: Call search.cfm and get the attributes (name, address, tel_no, etc…). Afterwards, display the results on an Ext.Window.
- 2nd Request: Display the geometries on OpenLayers using GML.
Read more…
Here is a list of the most widely used OGR commands I use..
OGR2OGR
1. POSTGRES -> MAPINFO
$ ogr2ogr -f "Mapinfo File" busline_buffer10m.tab PG:"host=localhost user=postgres dbname=cybersoftbj" -sql "select * from table_name" -a_srs WGS84 -nln layer_name -nlt MULTIPOLYGON
2. MAPINFO -> POSTGRES
ogr2ogr -f "PostgreSQL" PG:"host=127.0.0.1 user=rupert dbname=australia password=*****" AUS_ROAD.TAB -nln AUS_ROAD -a_srs EPSG:4269 -t_srs EPSG:3857 -skip-failures
ogr2ogr -f "PostgreSQL" PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" mytabfile.tab -nln newtablename -select columnName
Note: If you have Chinese characters, might as well do MAPINFO -> SHAPE -> POSTGRES
http://222.128.19.19/wordpress/?p=108
3. SHAPE -> POSTGRES
shp2pgsql -W "gbk" -s 4326 lbjrdnt_small_polyline roads > roads.sql
4. POSTGRES -> SHAPE
pgsql2shp -h 127.0.0.1 -u lbs -P tracking -f roads.shp databasename tablename
4. MAPINFO TO ORACLE
ogr2ogr -f OCI OCI:username/password@orcl C:\path_to_tabfile\EMPLOYEES.TAB -nln employees
Note: This assumes you already have Oracle 10g Client installed and “orcl” is defined as an instance in tnsnames.ora. OGR2OGR automatically updates USER_SDO_GEOM_METADATA and creates a spatial index.
5. MAPINFO to MAPINFO but different projection. From EPSG:4326 to EPSG:3857
ogr2ogr -f "MapInfo File" BaseMaps_3857/AUS_CITIES_3857.TAB BaseMaps/AUS_CITIES.TAB -a_srs "EPSG:4326" -t_srs "EPSG:3857"
Here is a post in buffering points and combining them using GeomUnion. Works in GEOS3.0.0, so note that your POSTGIS installation in windows contains a lower version of GEOS.
DROP FUNCTION combineGeometry();
CREATE FUNCTION combineGeometry() RETURNS Void AS'
DECLARE
--define datatypes here
updateCount integer DEFAULT 0;
geom_record RECORD;
geom_current RECORD;
geom_final RECORD;
mytotal integer DEFAULT 0;
BEGIN
--get the total
SELECT count(*) as mycount FROM busstopv1_buffer_20_temp INTO mytotal;
-- 1,2,3,4,5
FOR geom_record IN SELECT * FROM busstopv1_buffer_20 LOOP
--get the current geom of the record
SELECT *
FROM busstopv1_buffer_20_temp b
WHERE b.gid = geom_record.gid INTO geom_current;
RAISE INFO ''RECORD GID: %'', geom_record.gid;
RAISE INFO ''===========TOTAL COUNT: %=============='', mytotal;
--find the intersection of the current geom with other spatial entities
--and loop through that. For each loop, update the geom.
IF geom_current IS NULL THEN
CONTINUE;
END IF;
FOR geom_final IN
SELECT GeomUnion(b0.the_geom, geom_current.the_geom ) AS geom_current_union, b0.gid
FROM busstopv1_buffer_20_temp b0
WHERE b0.gid <> geom_current.gid
AND Intersects(b0.the_geom, geom_current.the_geom) = ''t''
ORDER BY gid ASC LOOP
--geom_current.gid = geom_record.gid
UPDATE busstopv1_buffer_20_temp
SET the_geom = GeomUnion( the_geom, geom_final.geom_current_union )
WHERE gid = geom_current.gid;
RAISE INFO ''UPDATED GID:%'', geom_current.gid;
DELETE FROM busstopv1_buffer_20_temp WHERE gid = geom_final.gid;
RAISE INFO ''DELETED GID:%'', geom_final.gid;
mytotal = mytotal - 1;
END LOOP;
END LOOP;
END;'
LANGUAGE plpgsql;
SELECT combineGeometry();
Next step.. trying out CGAL.
Here is a sample script in using buffer in Postgis. I buffered the line by 10 and 20 meters. Take note that I have to transform the geometry to the corresponding EPSG, so I could specify “meters”.
DROP TABLE busline_buffer1;
DELETE FROM geometry_columns WHERE f_table_name = 'busline_buffer1';
CREATE TABLE busline_buffer1( gid serial, CONSTRAINT pk_buffer1 PRIMARY KEY(gid));
SELECT AddGeometryColumn('public', 'busline_buffer1', 'the_geom', 4326, 'POLYGON',2);
// This would display what the output of the geometry IS...
SELECT AsText( transform( ST_BUFFER( transform(v1.the_geom, 32650), 10 ), 4326 ) ) FROM buslinev1 v1;
INSERT INTO busline_buffer1(the_geom) SELECT transform( ST_BUFFER( transform(v1.the_geom, 32650), 10 ), 4326 ) FROM buslinev1 v1;
SELECT gid, AsText(the_geom) FROM busline_buffer1;
========================================
DROP TABLE busline_buffer2;
DELETE FROM geometry_columns WHERE f_table_name = 'busline_buffer2';
CREATE TABLE busline_buffer2( gid serial, CONSTRAINT pk_buffer2 PRIMARY KEY(gid));
SELECT AddGeometryColumn('public', 'busline_buffer2', 'the_geom', 4326, 'POLYGON',2);
SELECT AsText( transform( ST_BUFFER( transform(v1.the_geom, 32650), 20 ), 4326 ) ) FROM buslinev1 v1;
INSERT INTO busline_buffer2(the_geom) SELECT transform( ST_BUFFER( transform(v1.the_geom, 32650), 20 ), 4326 ) FROM buslinev1 v1;
SELECT gid, AsText(the_geom) FROM busline_buffer2;
