Clever Geek Handbook

Using Variables in SQL 2008 to Return More than One Record

I'm pretty sure this is the wrong way to do it, so I'm looking for some suggestions.

I don't think my problem is that I am trying to solve a spatial problem. I'm just not sure if you can select latitude and longitude and return the geography type in the select statement. I have successfully created a geography column and entered 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
+1


source to share


2 answers


Can you elaborate on what you want to do?

If the question is about using text as I understand it, the WKT format is pretty normal here, so the problem is not a problem.

If you want to return multiple geometry values ​​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 a query like this:

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 dataset

+1


source


The geometry type should only be used for predicted data. Since you already have undesigned lat / long data, you must use the geography type. In addition, there is a static method for creating points:



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


source






More articles:


All Articles