Showing two spatial columns in the spatial results together

Nov 28, 2012 at 4:43 PM

Hi,

I am new to this and I have some geo points that does not intersects with my geo structures and I want to plot these and see where these points are in order to debug the solution.

I run the following query in MS SQL management studio:

 

 

select

 

top 100

geography::STPointFromText('POINT(' + CAST(ddLongitude AS VARCHAR) + ' ' + CAST(ddLatitude AS VARCHAR) + ')', 4326)

,geom

from Area, Position

where

geography

::STPointFromText('POINT(' + CAST(ddLongitude AS VARCHAR) + ' ' + CAST(ddLatitude AS VARCHAR) + ')', 4326).STIntersects(Area.geom) = 0

 

I can switch between the two columns in the Spatial Results window but I want to see the points and my shapes together.  Is there a way to do this?

Thank you for your replies


Nov 28, 2012 at 6:16 PM

You could try something like this:

select top 100 geography::Point(ddLatitude, ddLongitude, 4326) as point, geom into #foo

from Area, Position

where geography::Point(ddLatitude, ddLongitude, 4326).STIntersects(Area.geom) = 0

select point.STBuffer(1000) from #foo

union all

select geom from #foo

The general approach would be to populate a temporary table with the output from the select (into #foo). Then you can logically merge the two spatial columns in #foo using the union all command.

Note the simplified point constructor, Point(). When you are using the geography type and the Point() method in argument list is LATITUDE, LONGITUDE, SRID.

Please let us know if this answers your question.

Thanks,

-Ed

From: Viking42 [email removed]
Sent: Wednesday, November 28, 2012 8:44 AM
To: Ed Katibah
Subject: Showing two spatial columns in the spatial results together [sqlspatialtools:404916]

From: Viking42

Hi,

I am new to this and I have some geo points that does not intersects with my geo structures and I want to plot these and see where these points are in order to debug the solution.

I run the following query in MS SQL management studio:

select

top 100

geography::STPointFromText('POINT(' + CAST(ddLongitude AS VARCHAR) + ' ' + CAST(ddLatitude AS VARCHAR) + ')', 4326)

,geom

from Area, Position

where

geography

::STPointFromText('POINT(' + CAST(ddLongitude AS VARCHAR) + ' ' + CAST(ddLatitude AS VARCHAR) + ')', 4326).STIntersects(Area.geom) = 0

I can switch between the two columns in the Spatial Results window but I want to see the points and my shapes together. Is there a way to do this?

Thank you for your replies



Nov 30, 2012 at 7:50 AM

works like a charm!

Thank you very much