-- +-------------------------------------------------------------------------------------------------------------+
-- ¦      Author: < Franz Ayestaran >                                                                            ¦
-- ¦        Date: < Created 14/05/12, Revision 15/05/12 >                                                        ¦
-- ¦     Website: < www.GeoSpatialMapper.com >                                                                   ¦
-- ¦ Description: < The generated SQL 2008 / 2012 insert statement requires the map center to be set and the     ¦
-- ¦                generated KML to be viewable in this text area. The spatial information requires a minimum   ¦
-- ¦                of 5 longitude and latitude points otherwise it will be set to null. >                       ¦
-- +-------------------------------------------------------------------------------------------------------------+

CREATE TABLE [dbo].[tblGoogleMap](
	[fldId] [int] IDENTITY(1,1) NOT NULL,
	[fldName] [nvarchar](50) NULL,
	[fldLocation] [geography] NULL,
	[fldSpatial] [geography] NULL,
	[fldKml] [nvarchar](max) NULL
 CONSTRAINT [PK_tblVenue] PRIMARY KEY CLUSTERED
(
	[fldId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--------------------------------------
-- Example 'Polyline' Insert Statement
--------------------------------------

INSERT INTO tblGoogleMap ([fldName],[fldLocation],[fldSpatial],[fldKml]) VALUES ('Lord''s Cricket Ground - Polyline', GEOGRAPHY::Point(51.529312, -0.172669, 4326), GEOGRAPHY::STGeomFromText('LINESTRING(
-0.174826 51.528861,
-0.174236 51.529155,
-0.174450 51.529395,
-0.174354 51.529709,
-0.172927 51.530410,
-0.172863 51.530370,
-0.172573 51.530430,
-0.172176 51.530376,
-0.171886 51.530203,
-0.171693 51.530196,
-0.171489 51.530063,
-0.171479 51.529956,
-0.171532 51.529902,
-0.171393 51.529729,
-0.171371 51.529502,
-0.171167 51.529489,
-0.171275 51.529275,
-0.171479 51.529075,
-0.171811 51.528928,
-0.172112 51.528788,
-0.172219 51.528834,
-0.172648 51.528601,
-0.172884 51.528521,
-0.173195 51.528514,
-0.173410 51.528554,
-0.173667 51.528694,
-0.173828 51.528834,
-0.174450 51.528541,
-0.174837 51.528841,
-0.174826 51.528861)', 4326), '<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><name>Geo Spatial Mapper</name>
<description>Geo Spatial Online mapping tool in order to create polyline and polygon code using Google Maps version 3</description>
<Style id="Lump">
<LineStyle><color>CD0000FF</color><width>2</width></LineStyle>
<PolyStyle><color>9AFF0000</color></PolyStyle>
</Style>
<Style id="Path">
<LineStyle><color>FF0000FF</color><width>3</width></LineStyle>
</Style>
<Style id="markerstyle">
<IconStyle><Icon><href>
http://maps.google.com/intl/en_us/mapfiles/ms/micons/red-dot.png
</href></Icon></IconStyle>
</Style>
<Placemark><name>Lords''s Cricket Ground Polyline</name>
<description>Lord''s has long been seen as the ''Home of Cricket'' and the game''s spiritual ''headquarters''. But its importance is not merely historical. In practice it remains, to this day, perhaps the most important single place in world cricket.</description>
<styleUrl>#Path</styleUrl>
<LineString>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<coordinates>
-0.174826,51.528861,0
-0.174236,51.529155,0
-0.174450,51.529395,0
-0.174354,51.529709,0
-0.172927,51.530410,0
-0.172863,51.530370,0
-0.172573,51.530430,0
-0.172176,51.530376,0
-0.171886,51.530203,0
-0.171693,51.530196,0
-0.171489,51.530063,0
-0.171479,51.529956,0
-0.171532,51.529902,0
-0.171393,51.529729,0
-0.171371,51.529502,0
-0.171167,51.529489,0
-0.171275,51.529275,0
-0.171479,51.529075,0
-0.171811,51.528928,0
-0.172112,51.528788,0
-0.172219,51.528834,0
-0.172648,51.528601,0
-0.172884,51.528521,0
-0.173195,51.528514,0
-0.173410,51.528554,0
-0.173667,51.528694,0
-0.173828,51.528834,0
-0.174450,51.528541,0
-0.174837,51.528841,0
</coordinates>
</LineString>
</Placemark>
</Document>
</kml>')

-------------------------------------
-- Example 'Polygon' Insert Statement
-------------------------------------

INSERT INTO tblGoogleMap ([fldName],[fldLocation],[fldSpatial],[fldKml]) VALUES ('Lord''s Cricket Ground - Polygon', GEOGRAPHY::Point(51.529312, -0.172669, 4326), GEOGRAPHY::STGeomFromText('POLYGON((51.528861 -0.174826, 51.529155 -0.174236, 51.529395 -0.174450, 51.529709 -0.174354, 51.530410 -0.172927, 51.530370 -0.172863, 51.530430 -0.172573, 51.530376 -0.172176, 51.530203 -0.171886, 51.530196 -0.171693, 51.530063 -0.171489, 51.529956 -0.171479, 51.529902 -0.171532, 51.529729 -0.171393, 51.529502 -0.171371, 51.529489 -0.171167, 51.529275 -0.171275, 51.529075 -0.171479, 51.528928 -0.171811, 51.528788 -0.172112, 51.528834 -0.172219, 51.528601 -0.172648, 51.528521 -0.172884, 51.528514 -0.173195, 51.528554 -0.173410, 51.528694 -0.173667, 51.528834 -0.173828, 51.528541 -0.174450, 51.528841 -0.174837, 51.528861 -0.174826))', 4326), '<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><name>Geo Spatial Mapper</name>
<description>Geo Spatial Online mapping tool in order to create polyline and polygon code using Google Maps version 3</description>
<Style id="Lump">
<LineStyle><color>CD0000FF</color><width>2</width></LineStyle>
<PolyStyle><color>9AFF0000</color></PolyStyle>
</Style>
<Style id="Path">
<LineStyle><color>FF0000FF</color><width>3</width></LineStyle>
</Style>
<Style id="markerstyle">
<IconStyle><Icon><href>
http://maps.google.com/intl/en_us/mapfiles/ms/micons/red-dot.png
</href></Icon></IconStyle>
</Style>
<Placemark><name>Lords''s Cricket Ground Polygon</name>
<description>Lord''s has long been seen as the ''Home of Cricket'' and the game''s spiritual ''headquarters''. But its importance is not merely historical. In practice it remains, to this day, perhaps the most important single place in world cricket.</description>
<styleUrl>#Lump</styleUrl>
<Polygon>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<outerBoundaryIs><LinearRing><coordinates>
-0.174826,51.528861,0
-0.174236,51.529155,0
-0.174450,51.529395,0
-0.174354,51.529709,0
-0.172927,51.530410,0
-0.172863,51.530370,0
-0.172573,51.530430,0
-0.172176,51.530376,0
-0.171886,51.530203,0
-0.171693,51.530196,0
-0.171489,51.530063,0
-0.171479,51.529956,0
-0.171532,51.529902,0
-0.171393,51.529729,0
-0.171371,51.529502,0
-0.171167,51.529489,0
-0.171275,51.529275,0
-0.171479,51.529075,0
-0.171811,51.528928,0
-0.172112,51.528788,0
-0.172219,51.528834,0
-0.172648,51.528601,0
-0.172884,51.528521,0
-0.173195,51.528514,0
-0.173410,51.528554,0
-0.173667,51.528694,0
-0.173828,51.528834,0
-0.174450,51.528541,0
-0.174837,51.528841,0
-0.174826,51.528861,0
</coordinates></LinearRing></outerBoundaryIs>
</Polygon>
</Placemark>
</Document>
</kml>')

---------------------------------------
-- Example 'Rectangle' Insert Statement
---------------------------------------

INSERT INTO tblGoogleMap ([fldName],[fldLocation],[fldSpatial],[fldKml]) VALUES ('Lord''s Cricket Ground - Rectangle', GEOGRAPHY::Point(51.529552, -0.172884, 4326), GEOGRAPHY::STGeomFromText('LINESTRING(
-0.174622 51.530503,
-0.174622 51.528441,
-0.171103 51.528441,
-0.171103 51.530503,
-0.174622 51.530503)', 4326), '<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><name>Geo Spatial Mapper</name>
<description>Geo Spatial Online mapping tool in order to create polyline and polygon code using Google Maps version 3</description>
<Style id="Lump">
<LineStyle><color>CD0000FF</color><width>2</width></LineStyle>
<PolyStyle><color>9AFF0000</color></PolyStyle>
</Style>
<Style id="Path">
<LineStyle><color>FF0000FF</color><width>3</width></LineStyle>
</Style>
<Style id="markerstyle">
<IconStyle><Icon><href>
http://maps.google.com/intl/en_us/mapfiles/ms/micons/red-dot.png
</href></Icon></IconStyle>
</Style>
<Placemark><name>Lords''s Cricket Ground Rectangle</name>
<description>Lord''s has long been seen as the ''Home of Cricket'' and the game''s spiritual ''headquarters''. But its importance is not merely historical. In practice it remains, to this day, perhaps the most important single place in world cricket.</description>
<styleUrl>#Lump</styleUrl>
<Polygon>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<outerBoundaryIs><LinearRing><coordinates>
-0.174622,51.530503,0
-0.174622,51.528441,0
-0.171103,51.528441,0
-0.171103,51.530503,0
-0.174622,51.530503,0
</coordinates></LinearRing></outerBoundaryIs>
</Polygon>
</Placemark>
</Document>
</kml>')

-----------------------------------
-- Example 'Point' Insert Statement
-----------------------------------

INSERT INTO tblGoogleMap ([fldName],[fldLocation],[fldSpatial],[fldKml]) VALUES ('Lord''s Cricket Ground - Point', GEOGRAPHY::Point(51.529552, -0.172884, 4326), GEOGRAPHY::Point(-0.173195, 51.528567, 4326), '<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><name>Geo Spatial Mapper</name>
<description>Geo Spatial Online mapping tool in order to create polyline and polygon code using Google Maps version 3</description>
<Style id="Lump">
<LineStyle><color>CD0000FF</color><width>2</width></LineStyle>
<PolyStyle><color>9AFF0000</color></PolyStyle>
</Style>
<Style id="Path">
<LineStyle><color>FF0000FF</color><width>3</width></LineStyle>
</Style>
<Style id="markerstyle">
<IconStyle><Icon><href>
http://maps.google.com/intl/en_us/mapfiles/ms/micons/red-dot.png
</href></Icon></IconStyle>
</Style>
<Placemark><name>Lords''s Cricket Ground Point</name>
<description>Lord''s has long been seen as the ''Home of Cricket'' and the game''s spiritual ''headquarters''. But its importance is not merely historical. In practice it remains, to this day, perhaps the most important single place in world cricket.</description>
<styleUrl>#markerstyle</styleUrl>
<Point>
<coordinates>-0.173195,51.528567,0</coordinates>
</Point>
</Placemark>
</Document>
</kml>')

----------------------------------------
-- Example 'Directions' Insert Statement
----------------------------------------

INSERT INTO tblGoogleMap ([fldName],[fldLocation],[fldSpatial],[fldKml]) VALUES ('Lord''s Cricket Ground - Directions', GEOGRAPHY::Point(51.529632, -0.173764, 4326), GEOGRAPHY::STGeomFromText('LINESTRING(
-0.174570 51.531260,
-0.173990 51.530500,
-0.174020 51.530450,
-0.174380 51.530270,
-0.176200 51.529380,
-0.176110 51.529300,
-0.175280 51.528610,
-0.175170 51.528510)', 4326), '<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document><name>Geo Spatial Mapper</name>
<description>Geo Spatial Online mapping tool in order to create polyline and polygon code using Google Maps version 3</description>
<Style id="Lump">
<LineStyle><color>CD0000FF</color><width>2</width></LineStyle>
<PolyStyle><color>9AFF0000</color></PolyStyle>
</Style>
<Style id="Path">
<LineStyle><color>FF0000FF</color><width>3</width></LineStyle>
</Style>
<Style id="markerstyle">
<IconStyle><Icon><href>
http://maps.google.com/intl/en_us/mapfiles/ms/micons/red-dot.png
</href></Icon></IconStyle>
</Style>
<Placemark><name>Lords''s Cricket Ground Directions</name>
<description>Lord''s has long been seen as the ''Home of Cricket'' and the game''s spiritual ''headquarters''. But its importance is not merely historical. In practice it remains, to this day, perhaps the most important single place in world cricket.</description>
<styleUrl>#Path</styleUrl>
<LineString>
<tessellate>1</tessellate>
<altitudeMode>clampToGround</altitudeMode>
<coordinates>
-0.174570,51.531260,0
-0.173990,51.530500,0
-0.174020,51.530450,0
-0.174380,51.530270,0
-0.176200,51.529380,0
-0.176110,51.529300,0
-0.175280,51.528610,0
-0.175170,51.528510,0
</coordinates>
</LineString>
</Placemark>
<Placemark><name>Marker 1</name>
<description>43 Elm Tree Rd, London, Greater London NW8 9JR, UK</description>
<styleUrl>#markerstyle</styleUrl>
<Point>
<coordinates>-0.174570,51.531260,0</coordinates>
</Point>
</Placemark>
<Placemark><name>Marker 2</name>
<description>70 Grove End Rd, City of Westminster, NW8, UK</description>
<styleUrl>#markerstyle</styleUrl>
<Point>
<coordinates>-0.175170,51.528510,0</coordinates>
</Point>
</Placemark>
</Document>
</kml>')

SELECT * FROM tblGoogleMap WHERE fldId = 2