Using Variables in SQL 2008 to return more than one record

Question

I'm pretty sure this is not the right way to do this so I'm looking for some suggestions.

I don't think my problem so much is that I'm trying to solve a spatial problem. I'm just not sure of a good way to take the latitude and longitude and return a geography data type in a select statement. I have successfully created a geography column and input the data though.

Use thedatabase;
GO
Declare @Lat numeric(11,6)
Declare @Long numeric(11,6)
DECLARE @g geometry;




Select @Lat = Latitude, @Long = Longitude
from dbo.thetable

set @g = geometry::STGeomFromText('POINT (' + Cast(@Lat as varchar(30)) + ' ' + CAST(@Long as varchar(30)) + ')', 0);


select @g

Answer 1

Can you clarify what you want to do?

If the question related to the use of text - as I understand it, the WKT format is pretty normal here, so not a problem.

If you want to return multiple geometry values, something like:

SELECT geometry::STGeomFromText('POINT (' + CAST(Latitude as varchar(30))
     + ' ' + CAST(Longitude as varchar(30)) + ')', 0)
FROM dbo.thetable

If you want to work with multiple geometries, declare a table variable and insert the results of such a query:

DECLARE @data TABLE (location geometry)
INSERT @data (location)
SELECT geometry::STGeomFromText('POINT (' + CAST(Latitude as varchar(30))
     + ' ' + CAST(Longitude as varchar(30)) + ')', 0)
FROM dbo.thetable

and work with @data as a set of data

Answer 2

Geometry type should be used only for projected data. Since you already have unprojected lat/long data you should use Geography type instead. Also, there is a static method for creating points:

SELECT geography::Point(t.lat, t.lon, 0) FROM yourtable t