Updating Geography Type from (Long, Lat) data

Question

Post reply

Updating Geography Type from (Long, Lat) data

pietlinden

SSC Guru

Points: 63503
More actions
February 27, 2012 at 3:57 pm

#251445

I have the following table, and I'm trying to update the Location column with a Point calculated from the Longitude and Latitude.
CREATE TABLE [dbo].[House](
[HouseID] [int] IDENTITY(1,1) NOT NULL,
[StreetAddress] [varchar](100) NOT NULL,
[City] [varchar](25) NOT NULL,
[HState] [char](2) NOT NULL,
[ZipCode] [char](10) NULL,
[Location] [geography] NULL,
[TotalBudget] [money] NULL,
[Longitude] [float] NULL,
[Latitude] [float] NULL
) ON [PRIMARY]
Here's the SQL that's giving me the Longitude and Latitude... I just can't get the update to work...
SELECT HouseID, StreetAddress, CityState, PA.Zip, PA.County, PA.Latitude, PA.Longitude
FROM vw_HouseAddress CROSS APPLY dbo.ProcessAddress(StreetAddress, CityState, NULL) PA
How do I pass pa.Longitude and pa.Latitude to the Geography type in an update?
All this is so that I can create a map of repairs... the report is easy, it's the Geography stuff that's giving me fits!
Oh, right... the ProcessAddress stuff is from this, YAddressSqlFunction, which I found on CodePlex.
Thanks!
Pieter

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

Try the following...

UPDATE dbo.House set [Location] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(10)) + ' ' +

CAST([Latitude] AS VARCHAR(10)) + ')', 4326)

pietlinden SSC Guru Points: 63503 More actions · Answer 2

HappyCat,

Thanks for answering, but mission failed! Your SQL ran, but nothing got updated.

It would appear that for some reason, I cannot use CROSS APPLY in an update query.... If I use

Works a champ in a SELECT:

select pa.Longitude, pa.Latitude, geography::Point(pa.Longitude, pa.Latitude, 4326) As MyPoint, StreetAddress, House.City, HState

FROM House

CROSS APPLY ProcessAddress(StreetAddress, House.City + ', ' + HState, NULL) pa

but when I try this, it won't compile...

UPDATE House

SET Location = geography::Point(pa.Longitude, pa.Latitude, 4326)

CROSS APPLY ProcessAddress(StreetAddress, House.City + ', ' + HState, NULL) pa;

Is this possible in plain SQL or should I try this in SSIS or something? Or is there something funky about CROSS APPLY that I just plain don't understand? In ProcessAddress, the table returned contains the Longitude and Latitude values I want, but for some reason, I cannot pass them to the Geography::Point and make them work... What gives?

Thanks!

Pieter

pietlinden SSC Guru Points: 63503 More actions · Answer 3

This was interesting... Ran this

CREATE PROC uspHouseRepairs

AS

SELECT pa.Longitude, pa.Latitude, geography::Point(pa.Longitude, pa.Latitude, 4326) As MyPoint, h.Location, h.StreetAddress, h.City, h.HState, RepairList.RepairName, RepairList.RequiresLicense, HouseRepairs.StartDate,

HouseRepairs.EndDate

FROM House AS h INNER JOIN

HouseRepairs ON h.HouseID = HouseRepairs.HouseID INNER JOIN

RepairList ON HouseRepairs.RepairID = RepairList.RepairID

CROSS APPLY ProcessAddress(h.StreetAddress, h.City + ', ' + h.HState,null) pa;

exec uspHouseRepairs;

inside SQL Server, and it worked great - I got the Point I was expecting - looks like this:

0xE6100000010C2DD1596611AD55C048FB1F60AD1E4240

When I called the same stored procedure from within Reporting Services, I somehow get this in the MyPoint column:

POINT(-86.828993,36.142512)

Time to go look up Spatial Ed's notes? Maybe he can explain this to a dummy like me???