《实验i 创建地图》由会员分享，可在线阅读，更多相关《实验i 创建地图（4页珍藏版）》请在金锄头文库上搜索。
1、Creating OGC conformance test map in SQL Server 2008By Morten24. 二月 2008 19:31The Simple Features Specification v1.1.0 has a conformance test based on Joes Blue Lake vicinity map, which roughly looks like this:To create the map in Microsoft SQL Server 2008, first create the tables:- LakesCREATE TABL
2、E lakes (fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),shore Geometry); /INTEGER 整数类型- Road Segments(分段、段落)CREATE TABLE road_segments (fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),aliases VARCHAR(64),num_lanes INTEGER,centerline Geometry);- Divided RoutesCREATE TABLE divided_routes (fid INT
3、EGER NOT NULL PRIMARY KEY,name VARCHAR(64),num_lanes INTEGER,centerlines Geometry);- ForestsCREATE TABLE forests (fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),boundary Geometry);- BridgesCREATE TABLE bridges (fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),position Geometry);- StreamsCREATE T
4、ABLE streams (fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),centerline Geometry);- BuildingsCREATE TABLE buildings (fid INTEGER NOT NULL PRIMARY KEY,address VARCHAR(64),position Geometry,footprint Geometry);- PondsCREATE TABLE ponds (fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),type VARCHAR
5、(64),shores Geometry);- Named PlacesCREATE TABLE named_places (fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),boundary Geometry);- Map Neatline(图表边线 )CREATE TABLE map_neatlines (fid INTEGER NOT NULL PRIMARY KEY,neatline Geometry);Next step is to insert the rows:- LakesINSERT INTO lakes VALUES (10
6、1, BLUE LAKE,Geometry:STPolyFromText(POLYGON(52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18), 101);- Road segmentsINSERT INTO road_segments VALUES(102, Route 5, NULL, 2,Geometry:STLineFromText(LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 ) ,101);INSERT INTO road_segments VALUES(103, Rou
7、te 5, Main Street, 4,Geometry:STLineFromText(LINESTRING( 44 31, 56 34, 70 38 ) ,101);INSERT INTO road_segments VALUES(104, Route 5, NULL, 2,Geometry:STLineFromText(LINESTRING( 70 38, 72 48 ) ,101);INSERT INTO road_segments VALUES(105, Main Street, NULL, 4,Geometry:STLineFromText(LINESTRING( 70 38, 8
8、4 42 ) ,101);INSERT INTO road_segments VALUES(106, Dirt Road by Green Forest, NULL, 1,Geometry:STLineFromText(LINESTRING( 28 26, 28 0 ),101);- DividedRoutesINSERT INTO divided_routes VALUES(119, Route 75, 4,Geometry:STMLineFromText(MULTILINESTRING(10 48,10 21,10 0),(16 0,16 23,16 48), 101);- Forests
9、INSERT INTO forests VALUES(109, Green Forest,Geometry:STMPolyFromText(MULTIPOLYGON(28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18), 101);- BridgesINSERT INTO bridges VALUES(110, Cam Bridge, Geometry:STPointFromText(POINT( 44 31 ), 101);- StreamsINSERT INTO
10、streams VALUES(111, Cam Stream,Geometry:STLineFromText(LINESTRING( 38 48, 44 41, 41 36, 44 31, 52 18 ), 101);INSERT INTO streams VALUES(112, NULL,Geometry:STLineFromText(LINESTRING( 76 0, 78 4, 73 9 ), 101);- BuildingsINSERT INTO buildings VALUES(113, 123 Main Street,Geometry:STPointFromText(POINT(
11、52 30 ), 101),Geometry:STPolyFromText(POLYGON( ( 50 31, 54 31, 54 29, 50 29, 50 31) ), 101);INSERT INTO buildings VALUES(114, 215 Main Street,Geometry:STPointFromText(POINT( 64 33 ), 101),Geometry:STPolyFromText(POLYGON( ( 66 34, 62 34, 62 32, 66 32, 66 34) ), 101);- PondsINSERT INTO ponds VALUES(12
12、0, NULL, Stock Pond,Geometry:STMPolyFromText(MULTIPOLYGON( ( ( 24 44, 22 42, 24 40, 24 44) ),( ( 26 44, 26 40, 28 42, 26 44) ) ), 101);- Named PlacesINSERT INTO named_places VALUES(117, Ashton,Geometry:STPolyFromText(POLYGON( ( 62 48, 84 48, 84 30, 56 30, 56 34, 62 48) ), 101);INSERT INTO named_plac
13、es VALUES(118, Goose Island,Geometry:STPolyFromText(POLYGON( ( 67 13, 67 18, 59 18, 59 13, 67 13) ), 101);- Map NeatlinesINSERT INTO map_neatlines VALUES(115,Geometry:STPolyFromText(POLYGON( ( 0 0, 0 48, 84 48, 84 0, 0 0 ) ), 101); And voil. you can now for instance run all the conformance tests, bu
14、t the map is actually also a good base-map for learning the various spatial operations.If you use my SQL Spatial Query Tool, heres a query you can use to visualize the map:SELECT neatline, Background as name, map_neatlines as layer, White as FillColor, Transparent as LineColor, 1 as LineThickness FR
15、OM map_neatlines UNION ALLSELECT shores, name, ponds as layer, LightBlue as FillColor, Blue as LineColor, 1 as LineThickness FROM ponds UNION ALLSELECT boundary, name, forests as layer, Green as FillColor, #ff005500 as LineColor, 4 as LineThickness FROM Forests UNION ALL（允许重复值）SELECT shore, name, la
16、kes as layer, Blue as FillColor, Transparent as LineColor, 2 as LineThickness FROM lakes UNION ALLSELECT boundary, name, named_places as layer, #00ffff99 as FillColor, Brown as LineColor, 2 as LineThickness FROM named_places UNION ALLSELECT centerline, name, streams_outline as layer, Blue as FillColor, B
