|
|
After simple points time came to create polylines.
/*
create table with geography column and spatial index to store street lines
*/
CREATE TABLE [dbo].[milano_str](
[id] [int] NOT NULL,
[name] [nvarchar](100) NULL,
[type] [nvarchar](50) NULL,
[lat] FLOAT NULL,
[lon] FLOAT NULL,
[g] GEOGRAPHY
CONSTRAINT [PK_milano_str]
PRIMARY KEY CLUSTERED
([id] ASC))
ON [PRIMARY];
CREATE SPATIAL INDEX [geo_sidx] ON [dbo].[milano_str]
([g]) USING GEOGRAPHY_GRID
WITH (
GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
/*
add SRID constraint to ensure use of "WGS 84" spatial reference system
*/
ALTER TABLE [dbo].[milano_str]
WITH CHECK
ADD CONSTRAINT [enforce_srid_geography_milano_str]
CHECK ([g].[STSrid]=4326);
ALTER TABLE [dbo].[milano_str]
CHECK CONSTRAINT [enforce_srid_geography_milano_str];
|
Following select from milano street source data:
select lat + ' ' + lon + ', ' from dbo.milan_street_src
where street_id = 3
order by street_id, node_no;
|
Returns coordinates of line vertices. Line represents aproximate path of Via Dante.
| (No column name) |
| 9.182470116072778 45.46802940960921, |
| 9.183925755833585 45.46695843148468, |
| 9.185194564498829 45.46592556185459, |
| 9.186244976887451 45.465157677802, |
| 9.18723835284689 45.46453857917489, |
| 9.188393029018231 45.46380327025498, |
Create polyline from string. Copy-Paste from prev results:
INSERT INTO milano_str ([id],[name],[type],[g])
SELECT street_id, name, [type],
'LINESTRING (
9.182470116072778 45.46802940960921,
9.183925755833585 45.46695843148468,
9.185194564498829 45.46592556185459,
9.186244976887451 45.465157677802,
9.18723835284689 45.46453857917489,
9.188393029018231 45.46380327025498
)'
FROM dbo.milan_street_src
WHERE street_id=3
GROUP BY userid, [name],[type];
|
sqlexamples.info
|