May 8, 2011 at 12:29 am
I have a table of houses with addresses, which I eventually want to plot in a Report. I managed to stumble into part of the answer -- geocoding the addresses -- as shown below.
CREATE VIEW vw_LL_Address
AS
SELECT HouseNumber + ' ' + House.Street As AddressLine1, House.City + ', ' + House.[State] As AddressLine2, ProcessAddress.Latitude, ProcessAddress.Longitude
FROM House CROSS APPLY ProcessAddress(HouseNumber + ' ' + Street, City + ', ' + [State],null);
What I would really like to do, though, is update the House table's hLong and hLat columns with the results from ProcessAddress.Longitude and ProcessAddress.Latitude values.
I tried this
-- works fine
CREATE VIEW vw_LL_Address
AS
SELECT HouseID, HouseNumber + ' ' + House.Street As AddressLine1, House.City + ', ' + House.[State] As AddressLine2, ProcessAddress.Latitude, ProcessAddress.Longitude
FROM House CROSS APPLY ProcessAddress(HouseNumber + ' ' + Street, City + ', ' + [State],null);
UPDATE House
SET House.hLongitude = vw_LL_Address.Longitude,
House.hLatitude = vw_LL_Address.Latitude
WHERE House.HouseID = vw_LL_Address.HouseID;
Error: Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "vw_LL_Address.HouseID" could not be bound.
UPDATE House
SET House.hLongitude = ProcessAddress.Longitude,
House.hLatitude = ProcessAddress.Latitude
CROSS APPLY ProcessAddress(House.HouseNumber + ' ' + House.Street, House.City + ', ' + House.[State],null);
So I guess my question is How do I use construct the UPDATE query to properly update the hLongitude and hLatitude to the values returned by ProcessAddress.Longitude and ProcessAddress.Latitude?
I'm stymied!
Thanks!
Pieter
May 8, 2011 at 10:32 am
Try this:
-- you can use "SELECT *" before "FROM" to see the rows before the update
UPDATE h
SET h.hLongitude = pa.Longitude,
h.hLatitude = pa.Latitude
FROM House h
CROSS APPLY ProcessAddress( h.HouseNumber + ' ' + h.Street, h.City + ', ' + h.State, null ) pa
May 8, 2011 at 2:26 pm
Worked a champ! Thanks!
Pieter
March 9, 2012 at 4:11 pm
I can get the individual Longitude and Latitude values for the addresses in my table using this SQL:
UPDATE h
SET h.hLongitude = pa.Longitude,
h.hLatitude = pa.Latitude
FROM House h
CROSS APPLY ProcessAddress( h.HouseNumber + ' ' + h.Street, h.City + ', ' + h.State, null ) pa;
Thanks for getting me that far! Now I'm trying to use the Longitude and Latitude values returned by the ProcessAddress TVF to update a Geography datatype, so that when I create a report based on my data, I can get the locations to map properly.
I tried this SQL, and the parser doesn't like the CROSS APPLY:
UPDATE House
SET House.Location = Geography::PARSE('POINT(' + CAST(pa.Longitude AS VARCHAR(20)) + ' ' +
CAST(pa.Latitude AS VARCHAR(20)) + ')'
CROSS APPLY ProcessAddress(House.StreetAddress, House.City + ', ' + House.HState, 4326) pa;
So, what syntax do I use to update the Geography datatype? Here's the structure of the House table, just in case:
USE [FloodRepair]
GO
/****** Object: Table [dbo].[House] Script Date: 03/09/2012 17:09:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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]
GO
Is there any site that explains working with Geography datatypes to noobs? Ed Katibah's site is good, but waaaay over my head!
Thanks!
Pieter
March 9, 2012 at 11:27 pm
Mystery SOLVED!!! Here's the cheat code I found:
DECLARE @g geography = geography::STGeomFromText('POINT(' + CAST(@lng as Varchar(50)) + ' ' + CAST(@lat as varchar(50)) + ')', 4326)
which is here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=157630
Here's the complete update query that uses the ProcessAddress TVF:
UPDATE House
SET Location = Geography::STGeomFromText('POINT(' + CAST(pa.Longitude AS VARCHAR(50)) + ' ' + CAST(pa.Latitude AS VARCHAR(50)) + ')',4326)
FROM House
CROSS APPLY ProcessAddress(StreetAddress, City + ', ' + HState, 4326) pa;
The tricks were (1) use STGeomFromText and not Point, and to cast the values as VARCHAR(50).
"I am so smart, S-M-R-T!!! ... I mean S-M-A-R-T!!" (Thanks, Homer!)
March 10, 2012 at 12:11 am
Okay, sorry about all the noise. 😀
Problem solved!! Really this time! The map ROCKS!
Here's the update query that worked to fix/populate the Geography data type...
-- you can use "SELECT *" before "FROM" to see the rows before the update
UPDATE h
SET h.location = Geography::STGeomFromText('POINT(' + CAST(h.Longitude AS VARCHAR(50)) + ' ' + CAST(h.Latitude AS VARCHAR(50)) + ')',4326)
FROM House h
CROSS APPLY ProcessAddress( h.StreetAddress, h.City + ', ' + h.HState, null ) pa
The weird thing was that if you use something short like VARCHAR(25), all the points appear to be on top of one another in the map, and the Bing Map underneath doesn't work...
Hopefully this helps somebody! Took me long enough to figure out...
Pieter
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply