August 14, 2012 at 1:38 am
Hi
I have a location table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LocationCopy](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[LocationName] [varchar](100) NOT NULL,
[CityID] [int] NOT NULL,
[CityCode] [varchar](3) NOT NULL,
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[LocationDescription] [varchar](8000) NOT NULL,
[Image] [varchar](100) NULL,
[LocationGeography] [geography] NULL,
CONSTRAINT [PK_LocationCOPYID] PRIMARY KEY CLUSTERED
(
[LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Primary]
) ON [Primary]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[LocationCopy] ON
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (1, N'Thiruvananthapuram', 1, N'TRV', 8.4827, 76.9192, N'Thiruvananthapuram , also known as Trivandrum (Anglicised name), is the capital of the Indian state of Kerala and the headquarters of the Thiruvananthapuram District.', N'Loc_1.gif', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (2, N'Nedumbassery', 2, N'COK', 28.63531, 77.22496, N'Nedumbassery is a suburb of the city of Kochi situated 27 kilometres (17 mi) north of the city. The Cochin International Airport is located in Nedumbassery.', N'Loc_2.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (3, N'Kochi', 2, N'COK', 9.97814, 76.282697, N'Kochi (colonial name Cochin) is a vibrant city situated on the south-west coast of the Indian peninsula in the breathtakingly scenic and prosperous state of Kerala, hailed as ''God''s Own Country''. Its strategic importance over the centuries is underlined by the sobriquet Queen of the Arabian Sea. Informally, Cochin is also referred to as the Gateway to Kerala.', N'Loc_3.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (4, N'Munnar', 4, N'ID', 10.1, 77.0667, N'Munnar is a hill station in Kerala and in southern India. Munnar is located on the Western Ghats, situated in the Idukki.
The name Munnar is believed to mean "three rivers", referring to the town''s strategic location at the confluence of the Madhurapuzha, Nallathanni and Kundaly rivers. The town has shared a strong cultural link with Tamil Nadu.
The Munnar town is in Kannan Devan Hills ( KDH ) Village in Devikulam taluk and is the largest panchayat in the Idukki district having an area measuring nearly 557 km.
The nearest major railway stations are at Ernakulam and Aluva (approximately 120 km by road). The nearest airport is Cochin International Airport, which is 140 km away.', N'Loc_4.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (5, N'Fort Cochin', 2, N'COK', 9.9649619837, 76.2437438965, N'Fort Cochin is a region in the city of Kochi in the state of Kerala, India. This is part of a handful of water-bound regions toward the south-west of the mainland Kochi, and collectively known as Old Kochi or West Kochi. Adjacent to this is Mattancherry.
Fort Kochi is also home to one of India''s oldest churches - the St.Francis Church. This was a Roman Catholic Church during the Portuguese rule from 1503 to 1663, then a Dutch Reformist Church from 1664 to 1804, and Anglican Church from 1804 to 1947. Today it is governed by the Church of South India (CSI). Another important fact about the church is that Vasco Da Gama, who died in 1524, was buried here before his mortal remains were returned to Portugal 14 years later. Each and every structure, street, door, window and brick in Fort Kochi has several stories to tell.', N'Loc_5.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (6, N'Thekkady', 4, N'ID', 9.607526, 77.167622, N'Thekkady is located in the Idukki district of South Indian state, Kerala. Thekkady is situated towards the lower end of the state and close to the borders of the adjacent state, Tamil Nadu. The place is about 60 kms from the central Idukki and exists as a wonderful tourist place featuring hill stations, plantations and wildlife sanctuaries. Thekkady with its panoramic views of nature, is a popular tourist destination in the state of Kerala. The landscape is dominated by the wildlife sanctuary, low lying hills and plantation lands. The nature has a good hold on the lands in Thekkady. The wildlife sanctuary that lies sprawling here is one of the largest in India. The boat safari on the Periyar lake exposes one to the realms of natures and provides a chance to spot the wildlife in its natural habitat.', N'Loc_6.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (7, N'Kovalam', 1, N'TRV', 8.400923, 76.97883, N'Kovalam, known as the ‘Paradise of Southern India’ is just 16 kilometers away from Trivandrum city. The word ‘Kovalam’ means a grove of coconut trees. The coconut trees at the Kovalam villages epitomize natural beauty and economic value. A characteristic feature of the sands in the Kovalam beaches is that they are black in color, to an extent. This is attributed to the presence of thorazite and ilmenite.', N'Loc_7.gif', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (8, N'Marari', 3, N'AL', 9.491039, 76.320841, N'Marari originated from Mararikulam,a sleepy fishing village located between city of Cochin and Alleppey. Marari,situated in the heart of Spice Coast, is the most fabulous stretch of soft sandy beach, fringed with palm , specially for those who want complete relaxation. The beach is a picture perfect combination of nature where you can enjoy lush gorgeous greens, whispering sea breeze and murmuring surf.', N'Loc_8.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (9, N'Kumarakom', 8, N'KT', 9.5833333, 76.4333333, N'Kumarakom is a tourist village and in fact a cluster of little beautiful islands on the eastern banks of the Vembanadu Lake. It is 15 kilometers away from Kottyam town. The place Kumarakom is filled with the real beauty of Kerala with mangrove forests, lush green paddy fields, waterways, canals etc. Vembanad Lake is an enchanting picnic spot and a rapidly developing backwater tourism spot in Kerala. The Kerala Backwaters have now become synonym for Kerala worldwide.', N'Loc_9.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (10, N'Mananthavady', 14, N'WA', 11.8, 76, N'Mananthavady is a town and taluk in the Wayanad district of Kerala. This area was once ruled by the Pazhassi dynasty and the tomb of Pazhassi Raja is an important heritage site located in the heart of the town. This is also the headquarters of the Adivasi Gothra Mahasabha. It is the third biggest town in Wayanad District, after Sulthan Bathery and Kalpetta.', NULL, NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (11, N'Alleppey', 3, N'AL', 9.463821, 76.4013471, N'Alappuzha also known as Alleppey, is a town in Alappuzha District of Kerala state of southern India. This town is considered to be the oldest planned town in this region and the lighthouse built on the coast of the town is the first of its kind along the Arabian coast. Alleppey is situated 62 kilometres (39 mi) to the south of Kochi and 155 kilometres (96 mi) north of Trivandrum.[2] A town with picturesque canals, backwaters, beaches, and lagoons, it was described as the one of the places known as "Venice of the East".
Backwaters of Alleppey are world famous and is the most popular tourist attraction in Kerala. A houseboat cruise in these backwaters is a delightful experience.', N'Loc_11.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (12, N'Vagamon', 4, N'ID', 9.6887473, 76.905242, N'Vagamon is a hill station located in Kottayam- Idukki border of Kerala. It has a cool climate with the temperature between 10 and 23 °C during a summer midday. It is situated 1,100 meters above sea level. Vagamon also known as the ''Scotland of Asia'', was discovered by the Britishers who found the place ideal for tea plantations and they were followed by Christian missionaries who built their abode of service at Kurisumala in Vagamon.
Still untouched by commercialisation, the sleepy town with its scenic valleys, beautiful green meadows, enchanting pine forests, mists, fogs, tea estates, waterfalls with the overall greenery is really striking. The landscape is mostly hilly bald with green meadows. Vagamon is also home to a large variety of flowers and orchids.', N'Loc_12.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (13, N'Vythiri', 14, N'WA', 11.62106, 76.06101, N'Vythiri is a very exotic and picturesque place located in the Sylvan Northern High Ranges of Kerala in Wayanad District .It is known for its comfortable, refreshing and pollution free environment. Situated at an altitude of about 3750-4500 ft above sea level it spreads over an area of about 150 sq km, eco-friendly and designed in the traditional Kerala architectural style. Vythiri is world famous for its evergreen rain forests and plantations of coffee, tea, rubber, cardamom and pepper.', N'Loc_13.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (14, N'Wayanad', 14, N'WA', 11.7094457, 76.095537, N'Wayanad in the north-east of Kerala, India, was formed on November 1, 1980 as the 12th district by carving out areas from Kozhikode and Kannur districts. Kalpetta is the district headquarters as well as the only municipal town in the district. The region was known as Mayakshetra (Maya''s land) in the earliest records. Mayakshetra evolved into Mayanad and finally to Wayanad.[1] The Folk etymology of the word says it is a combination of Vayal (paddy field) and Naad (land), making it ''The Land of Paddy Fields''. There are many indigenous tribals in this area.', NULL, NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (15, N'Kozhikode', 9, N'KZ', 11.2587531, 75.78041, N'Kozhikode , also known as Calicut, is a city in the state of Kerala in southern India on the Malabar Coast. Kozhikode is the third largest city in Kerala and is part of the second largest urban agglomeration in Kerala.', NULL, NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (16, N'Vaikom', 8, N'KT', 9.746488, 76.392647, N'Vaikom is a taluk and also its capital town, situated in the North-West of Kottayam district in Kerala. Its western border is the Lake Vembanad, and is crossed by various estuaries of the River Muvattupuzha. Vaikom is the oldest township in Kottayam district, and among the oldest in South India.', NULL, NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (17, N'Kollam', 7, N'KL', 8.9620626176, 76.5511894226, N'Kollam, often anglicized as Quilon, is a city in the Indian state of Kerala. The city lies on the banks of Ashtamudi Lake on the Arabian sea coast and is situated about 71 kilometres (44 mi) north of the state capital, Thiruvananthapuram.
Kollam is the fourth-largest city in Kerala, after Thiruvananthapuram, Ernakulam, Kozhikode, (The new population is taken as city agglomeration) in Kerala on the basis of population and the fifth-largest city on the basis of area. It is known for cashew processing and coir manufacturing. It is considered to be the southern gateway to the backwaters of Kerala and is a prominent tourist destination.', N'Loc_17.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (18, N'Kannur', 5, N'KN', 11.8688889, 75.3555556, N'Kannur also known as Cannanore, is a city in Kannur district of Kerala. During British rule in India, Kannur was known by its old name Cannanore. . It is the largest city of North Malabar region. Kannur Municipality was established in 1867 and is one of the oldest municipalities in Kerala.Kannur is famous for its pristine beaches; Theyyam, its native performing art, and its handloom industry. Kannur is of great strategic military importance.', N'Loc_18.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (19, N'Varkala', 1, N'TRV', 8.7333, 76.7167, N'Varkala is a coastal town and municipality in Thiruvananthapuram district. It is located 50 kilometres (approx. 32 miles) north-west of Thiruvananthapuram (Trivandrum) and 37 km south-west of Kollam city.
Varkala is the only place in southern Kerala where cliffs are found adjacent to the Arabian Sea.', N'Loc_19.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (20, N'Thrissur', 13, N'TS', 10.52, 76.21, N'originally Thrisivaperoor and previously known by its anglicized form as Trichur, is the fourth largest city, the third largest urban agglomeration in Kerala.
Thrissur is also known as the Cultural Capital of Kerala because of its cultural, spiritual and religious leanings throughout history.', N'Loc_20.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (21, N'Kottayam', 8, N'KT', 9.58, 76.52, N'Kottayam is located in south-central Kerala. The city is approximately 150 km (93.2 mi) from the state capital, Thiruvananthapuram. The city is an important trading center of spices and commercial crops, especially rubber. Major Kerala print media such as Malayala Manorama, Deepika and Mangalam are headquartered in the town. Also a pioneering center of modern education in Kerala, the city became India''s first city to achieve 100% literacy in 1989 and the district became the first tobacco free district in India on September 28, 2008. The city of Kottayam is also known as Akshara Nagari (City of Alphabets) in honor of its contributions to print media and literature.', N'Loc_21.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (22, N'Pathanamthitta', 12, N'PT', 9.27, 76.78, N'Pathanamthitta is the smallest district of Kerala. The name Pathanamthitta is derived from two Malayalam words Pathanam” and “thitta”, which together mean "array of ten "family" houses by the riverside". The life of the people of the district and their cultural activities were shaped and influenced by the river Pampa. The congregation of the followers of the Orthodox Church at Maramon and that of Hindus at Cherukolpuzha on the sandy bed of Pampa every year are turned into occasions for learning lessons of human emanicipation and brotherhood.', N'Loc_22.jpg', NULL)
INSERT [dbo].[LocationCopy] ([LocationID], [LocationName], [CityID], [CityCode], [Latitude], [Longitude], [LocationDescription], [Image], [LocationGeography]) VALUES (23, N'moozhikulam', 13, N'TS', 1.256, 2.36, N'mnbjbdfa', N'Loc_23.jpg', NULL)
SET IDENTITY_INSERT [dbo].[LocationCopy] OFF
Whilusing this code it generating error,pls help me this is first time using Spatial
DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1
UPDATE [dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT(@Latitude @Longitude)', 4326)
WHERE LocationID = @ID
This is the error
Msg 6522, Level 16, State 1, Line 6
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24141: A number is expected at position 15 of the input. The input has @Latitude.
System.FormatException:
at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()
at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean parseParentheses)
at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
.
The statement has been terminated.
August 14, 2012 at 1:52 am
Does the below work?
DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1
UPDATE [MarvelTour_DEV].[dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT('@Longitude+' '+@Latitude+')', 4326)
WHERE LocationID = @ID
August 14, 2012 at 1:58 am
This was removed by the editor as SPAM
August 14, 2012 at 2:36 am
anthony.green (8/14/2012)
Does the below work?
DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1
UPDATE [dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT('@Longitude+' '+@Latitude+')', 4326)
WHERE LocationID = @ID
While wxecuting this query it gives error
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@Longitude'.
August 14, 2012 at 2:37 am
Stewart "Arturius" Campbell (8/14/2012)
Have you tried:
UPDATE [dbo].[LocationCopy]SET
LocationGeography = geography::Point(@Latitude, @Longitude, 4326)
WHERE LocationID = @ID
Thanks it worked
When i googled everyone using this method STGeomFromText
LocationGeography = geography::STGeomFromText('POINT(@Latitude @Longitude)', 4326)
What is the problem with this statement
August 14, 2012 at 2:39 am
Doh, forgot the +, should of been an easy one to spot
DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1
UPDATE [MarvelTour_DEV].[dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT('+@Longitude+' '+@Latitude+')', 4326)
WHERE LocationID = @ID
August 14, 2012 at 2:52 am
This was removed by the editor as SPAM
August 14, 2012 at 3:02 am
anthony.green (8/14/2012)
Doh, forgot the +, should of been an easy one to spot
DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1
UPDATE [MarvelTour_DEV].[dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT('+@Longitude+' '+@Latitude+')', 4326)
WHERE LocationID = @ID
this error is generated
Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to float.
August 14, 2012 at 3:06 am
As the error says your passing in floats and trying to build a string, change them in the declaration to varchar or convert in the query.
August 14, 2012 at 3:12 am
UPDATE [MarvelTour_DEV].[dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT('+CAST(@Longitude AS Varchar)+' '+CAST(@Latitude AS Varchar)+')', 4326)
WHERE LocationID = @ID
So these methods only take Varchar datat type only after converting to varchar it is working
Thanks
August 14, 2012 at 3:24 am
DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1
UPDATE [dbo].[LocationCopy]
SET
LocationGeography = geography::STGeomFromText('POINT('+CAST(@Latitude AS Varchar)+' '+CAST(@Longitude AS Varchar)+' )', 4326)
WHERE LocationID = @ID
SELECT CAST(LocationGeography AS varchar(max)) FROM dbo.LocationCopy
GO
DECLARE @Latitude float = 8.4827
DECLARE @Longitude float = 76.9192
DECLARE @ID int = 1
UPDATE [dbo].[LocationCopy]
SET
LocationGeography = geography::Point(@Latitude, @Longitude, 4326)
WHERE LocationID = @ID
SELECT CAST(LocationGeography AS varchar(max)) FROM dbo.LocationCopy
GO
After executing the updation with geography::STGeomFromText method
there is no change in the latitude,longitude order in the result
POINT (8.4827 76.9192)
but when i use geography::Point method there is a change in the latitude,longitude order in the result
POINT (76.9192 8.4827)
Why this happen?I am planning to use this to find nearby places, if the order get change doesn't it affect the distance.
One more question isn't the POINT method is fastest as there is no conversion..
August 14, 2012 at 3:26 am
The clue is in the spatial function STGeomFromText requires a text imput.
August 14, 2012 at 4:02 am
dilipd006 (8/14/2012)
After executing the updation with geography::STGeomFromText method
there is no change in the latitude,longitude order in the result
POINT (8.4827 76.9192)
but when i use geography::Point method there is a change in the latitude,longitude order in the result
POINT (76.9192 8.4827)
Why this happen?I am planning to use this to find nearby places, if the order get change doesn't it affect the distance.
The order of latitude and langitude is important as found from this site
http://www.mssqltips.com/sqlservertip/1965/sql-server-geography-data-type/
Finally, and perhaps most important, is the order in which the coordinates are entered in the data type. The functions used for the geography data types view coordinates as {X,Y} coordinates. In order to properly document the locations of the earthquakes I will need to present the data as longitude first, then latitude, which is different from how most people think of earth-related coordinates.
August 14, 2012 at 4:09 am
This was removed by the editor as SPAM
August 14, 2012 at 5:14 am
Stewart "Arturius" Campbell (8/14/2012)
Read up on the geography::Point, especially regarding the X (Latitude) and Y (Longitude) coordinates.
In this article it is written X=latitude, y= longitude..so when updating
i am passing the lattitude and langitude in (X,Y) format.
But when i am retrieving this the order changed
it return as (Y,X) format
Does'nt it should return in the same format as (x,y).?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply