DATA CONVERSION INTO SHP PostGIS FOR USE IN APPLICATION SAMPLE / DEMO WebGIS
6. Observe the sql file, whose contents would make the table and its content in accordance with shp files. example sql file content (road.sql): BEGIN; CREATE TABLE "troad" (gid serial PRIMARY KEY, "Fnode_" int8, "Tnode_" int8, "Lpoly_" int8, "Rpoly_" int8, "Length" float8, "Road_" int8, "Road_id" int8, "F_code" int2, "Name_e" varchar (26), "Name_f" varchar (26)); SELECT AddGeometryColumn troad ('',' ',' the_geom ',' -1 ',' MULTILINESTRING ', 2); INSERT INTO "troad" ("fnode_", "tnode_", "lpoly_", "rpoly_", "length", "road_", "road_id", "f_code", "name_e", "name_f", the_geom) VALUES ('65 ', '70', '3 ', '3', '31779 .260 ', '1', '1 ', '74', NULL, NULL, '01050000000100000001020000000400000000000000164F414100000060EB773 341000000A0EC494141000000808A71334100000080DF2B414100000080CE3E334100000000C41F414100000000E0273341 '); ... ... ... ... ... ... ... ... ... .... 7. load all these sql files into a PostgreSQL database by executing command with the format "psql-d [target_database]-f [file_sql]" in the SQL terminal monitor o psql-d-f dbspatialdemo road.sql o psql-d-f dbspatialdemo rail.sql o psql-d-f dbspatialdemo province.sql o psql-d-f dbspatialdemo popplace.sql o psql-d-f dbspatialdemo park.sql o psql-d-f dbspatialdemo land_fn.sql o psql-d-f dbspatialdemo grid.sql o psql-d-f dbspatialdemo fedlimit.sql o psql-d-f dbspatialdemo drainage.sql o psql-d-f dbspatialdemo drain_fn.sql Observe the results. • The column of type POINT geometry contained in the table: tpopplace • Column type MULTILINESTRING geometry contained in the table: troad; trail; tgrid; tfedlimit www.hatma.info 4 WebgGIS with MapServer - MapLab - Chameleon - PostGIS • Column type MULTIPOLYGON geometry contained in the table: tprovince; tpark; tland_fn; tdrainage; tdrain_fn 8. Create a GIST index on each column of type geometry at each table. Then do a vacuum analyze, to update the statistical geometry (do Vacuum regularly, especially when there are changes in spatial data) o CREATE INDEX troad_gist_index troad ON USING GIST (the_geom GIST_GEOMETRY_OPS ); VACUUM Analyze troad (the_geom); o CREATE INDEX trail trail_gist_index ON USING GIST (the_geom GIST_GEOMETRY_OPS); Analyze VACUUM trail (the_geom); o CREATE INDEX tprovince_gist_index tprovince ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tprovince (the_geom); o CREATE INDEX tpopplace_gist_index tpopplace ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tpopplace (the_geom); o CREATE INDEX tpark_gist_index tpark ON USING GIST (the_geom GIST_GEOMETRY_OPS ); VACUUM Analyze tpark (the_geom); o CREATE INDEX tland_fn_gist_index tland_fn ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tland_fn (the_geom); o CREATE INDEX tgrid_gist_index tgrid ON USING GIST (the_geom GIST_GEOMETRY_OPS ); VACUUM Analyze tgrid (the_geom); o CREATE INDEX tfedlimit_gist_index tfedlimit ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tfedlimit (the_geom); o CREATE INDEX tdrainage_gist_index tdrainage ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tdrainage (the_geom); o CREATE INDEX tdrain_fn_gist_index tdrain_fn ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tdrain_fn (the_geom); 9. Gid Create an index on each table. Due to speed up the query, then required a unique column in a table geometry, with the index that has been defined him: o CREATE INDEX ON troad_gid troad (gid); o CREATE INDEX ON trail_gid trail (gid); o CREATE INDEX ON tprovince_gid tprovince (gid); o CREATE INDEX ON tpopplace_gid tpopplace (gid); o CREATE INDEX ON tpark_gid tpark (gid); o CREATE INDEX ON tland_fn_gid tland_fn (gid); www.hatma.info 5 WebgGIS with MapServer - MapLab - Chameleon - PostGIS o CREATE INDEX ON tgrid_gid tgrid (gid); o CREATE INDEX ON tfedlimit_gid tfedlimit (gid); o CREATE INDEX ON tdrainage_gid tdrainage (gid); o CREATE INDEX ON tdrain_fn_gid tdrain_fn (gid); 10. See in pgAdmin, whether ten table and its contents have been entered in the database. If true, then you have successfully entered shp data into a table in the database. STEP I. CREATING A DATABASE Spatial 1. PgAdmin Run, and then create a new user WebgGIS with MapServer - MapLab - Chameleon - PostGIS Then enter the new user data 2. Create a new database, using template_postgis WebgGIS with MapServer - MapLab - Chameleon - PostGIS 3. Enter the database details 4. Copy the shp files are used in the sample application demo WebGIS above, from folder D: \ ms4w \ apps \ demo \ samples \ data into the bin directory on the server installation PostgreSQL (C: \ Program Files \ PostgreSQL \ 8.2 \ bin). 5. Then run "psql" SQL terminal monitor, and do the conversion of all files shp above into the form *. sql. Thus obtained ten sql file. o shp2pgsql road troad> road.sql o shp2pgsql rail trail> rail.sql o shp2pgsql province tprovince> province.sql o shp2pgsql popplace tpopplace> popplace.sql o shp2pgsql park tpark> park.sql o shp2pgsql land_fn tland_fn> land_fn.sql o shp2pgsql grid tgrid> grid.sql o shp2pgsql fedlimit tfedlimit> fedlimit.sql o shp2pgsql drainage tdrainage> drainage.sql o shp2pgsql drain_fn tdrain_fn> drain_fn.sql WebgGIS with MapServer - MapLab - Chameleon - PostGIS In the above discussion we have had some shp data used in sample / demo WebGIS, among others: o raster image: bathymetry layer (bath_mapserver.tif) o polygon: land_fn layer (land_fn.shp); layer park (park.shp); layer drain_fn (Drain_fn.shp); drainage layer (drainage.shp) o line: layer prov_bound (province.shp); layer fedlimit (fedlimit.shp); layer rail (rail.shp); Layer Road (road.shp); o point: layer popplace (popplace.shp); o grid: grid layer (grid.shp) Next we will change the above shp data into a table in the database PostgreSQL.
6. Observe the sql file, whose contents would make the table and its content in accordance with shp files. example sql file content (road.sql): BEGIN; CREATE TABLE "troad" (gid serial PRIMARY KEY, "Fnode_" int8, "Tnode_" int8, "Lpoly_" int8, "Rpoly_" int8, "Length" float8, "Road_" int8, "Road_id" int8, "F_code" int2, "Name_e" varchar (26), "Name_f" varchar (26)); SELECT AddGeometryColumn troad ('',' ',' the_geom ',' -1 ',' MULTILINESTRING ', 2); INSERT INTO "troad" ("fnode_", "tnode_", "lpoly_", "rpoly_", "length", "road_", "road_id", "f_code", "name_e", "name_f", the_geom) VALUES ('65 ', '70', '3 ', '3', '31779 .260 ', '1', '1 ', '74', NULL, NULL, '01050000000100000001020000000400000000000000164F414100000060EB773 341000000A0EC494141000000808A71334100000080DF2B414100000080CE3E334100000000C41F414100000000E0273341 '); ... ... ... ... ... ... ... ... ... .... 7. load all these sql files into a PostgreSQL database by executing command with the format "psql-d [target_database]-f [file_sql]" in the SQL terminal monitor o psql-d-f dbspatialdemo road.sql o psql-d-f dbspatialdemo rail.sql o psql-d-f dbspatialdemo province.sql o psql-d-f dbspatialdemo popplace.sql o psql-d-f dbspatialdemo park.sql o psql-d-f dbspatialdemo land_fn.sql o psql-d-f dbspatialdemo grid.sql o psql-d-f dbspatialdemo fedlimit.sql o psql-d-f dbspatialdemo drainage.sql o psql-d-f dbspatialdemo drain_fn.sql Observe the results. • The column of type POINT geometry contained in the table: tpopplace • Column type MULTILINESTRING geometry contained in the table: troad; trail; tgrid; tfedlimit www.hatma.info 4 WebgGIS with MapServer - MapLab - Chameleon - PostGIS • Column type MULTIPOLYGON geometry contained in the table: tprovince; tpark; tland_fn; tdrainage; tdrain_fn 8. Create a GIST index on each column of type geometry at each table. Then do a vacuum analyze, to update the statistical geometry (do Vacuum regularly, especially when there are changes in spatial data) o CREATE INDEX troad_gist_index troad ON USING GIST (the_geom GIST_GEOMETRY_OPS ); VACUUM Analyze troad (the_geom); o CREATE INDEX trail trail_gist_index ON USING GIST (the_geom GIST_GEOMETRY_OPS); Analyze VACUUM trail (the_geom); o CREATE INDEX tprovince_gist_index tprovince ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tprovince (the_geom); o CREATE INDEX tpopplace_gist_index tpopplace ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tpopplace (the_geom); o CREATE INDEX tpark_gist_index tpark ON USING GIST (the_geom GIST_GEOMETRY_OPS ); VACUUM Analyze tpark (the_geom); o CREATE INDEX tland_fn_gist_index tland_fn ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tland_fn (the_geom); o CREATE INDEX tgrid_gist_index tgrid ON USING GIST (the_geom GIST_GEOMETRY_OPS ); VACUUM Analyze tgrid (the_geom); o CREATE INDEX tfedlimit_gist_index tfedlimit ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tfedlimit (the_geom); o CREATE INDEX tdrainage_gist_index tdrainage ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tdrainage (the_geom); o CREATE INDEX tdrain_fn_gist_index tdrain_fn ON USING GIST (the_geom GIST_GEOMETRY_OPS); VACUUM Analyze tdrain_fn (the_geom); 9. Gid Create an index on each table. Due to speed up the query, then required a unique column in a table geometry, with the index that has been defined him: o CREATE INDEX ON troad_gid troad (gid); o CREATE INDEX ON trail_gid trail (gid); o CREATE INDEX ON tprovince_gid tprovince (gid); o CREATE INDEX ON tpopplace_gid tpopplace (gid); o CREATE INDEX ON tpark_gid tpark (gid); o CREATE INDEX ON tland_fn_gid tland_fn (gid); www.hatma.info 5 WebgGIS with MapServer - MapLab - Chameleon - PostGIS o CREATE INDEX ON tgrid_gid tgrid (gid); o CREATE INDEX ON tfedlimit_gid tfedlimit (gid); o CREATE INDEX ON tdrainage_gid tdrainage (gid); o CREATE INDEX ON tdrain_fn_gid tdrain_fn (gid); 10. See in pgAdmin, whether ten table and its contents have been entered in the database. If true, then you have successfully entered shp data into a table in the database. STEP I. CREATING A DATABASE Spatial 1. PgAdmin Run, and then create a new user WebgGIS with MapServer - MapLab - Chameleon - PostGIS Then enter the new user data 2. Create a new database, using template_postgis WebgGIS with MapServer - MapLab - Chameleon - PostGIS 3. Enter the database details 4. Copy the shp files are used in the sample application demo WebGIS above, from folder D: \ ms4w \ apps \ demo \ samples \ data into the bin directory on the server installation PostgreSQL (C: \ Program Files \ PostgreSQL \ 8.2 \ bin). 5. Then run "psql" SQL terminal monitor, and do the conversion of all files shp above into the form *. sql. Thus obtained ten sql file. o shp2pgsql road troad> road.sql o shp2pgsql rail trail> rail.sql o shp2pgsql province tprovince> province.sql o shp2pgsql popplace tpopplace> popplace.sql o shp2pgsql park tpark> park.sql o shp2pgsql land_fn tland_fn> land_fn.sql o shp2pgsql grid tgrid> grid.sql o shp2pgsql fedlimit tfedlimit> fedlimit.sql o shp2pgsql drainage tdrainage> drainage.sql o shp2pgsql drain_fn tdrain_fn> drain_fn.sql WebgGIS with MapServer - MapLab - Chameleon - PostGIS In the above discussion we have had some shp data used in sample / demo WebGIS, among others: o raster image: bathymetry layer (bath_mapserver.tif) o polygon: land_fn layer (land_fn.shp); layer park (park.shp); layer drain_fn (Drain_fn.shp); drainage layer (drainage.shp) o line: layer prov_bound (province.shp); layer fedlimit (fedlimit.shp); layer rail (rail.shp); Layer Road (road.shp); o point: layer popplace (popplace.shp); o grid: grid layer (grid.shp) Next we will change the above shp data into a table in the database PostgreSQL.