SQL Server Spatial Forum

how to convert STDistance value to KM

Thu, 01 Dec 2011 07:36:12 Z

Declare @Point1 Geometry,@Point2 Geometry

SET @Point1 = GEOMETRY::STGeomFromText('POINT (88.358206509833749 22.527348591128348)',18)

SET @Point2 = GEOMETRY::STGeomFromText('POINT (72.873565892186846 19.119301736552419)',18)

SELECT @Point1.STDistance(@Point2)

output 15.8552476619151

please tell me how to convert this value to Kilometers

Spatial index hints don't work in SQL Server 2008?

Tue, 08 May 2012 00:31:39 Z

Using the following

SELECT *  FROM dbo.GRSM_WETLAND_POLY     CROSS APPLY (SELECT TOP 1 Name, shape                   
FROM GRSM.dbo.GRSM_Trails --WITH(index S319_idx))                  
WHERE GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) IS NOT NULL
                  ORDER BY GRSM_Trails.Shape.STDistance(dbo.GRSM_WETLAND_POLY.Shape) ASC) fnc

runs very slow on 134 rows (56 seconds), however, with the index hint
uncommented, it returns

Msg 8635, Level 16, State 4, Line 3
The query processor could not produce
a query plan for a query with a spatial index hint. Reason: Spatial indexes do
not support the comparator supplied in the predicate. Try removing the index
hints or removing SET FORCEPLAN.

Execution plan shows the filter cost at 98%, it's querying against 1400 rows
in the other table, so the total cost is 134 * 1400 individual seeks, which is
where the delay is. On their own, the spatial indexes in each table perform
great, with no fragmentation, 99% page fulness, and use medium for all 4 grid
levels with 16 cells per object. Changing the spatial index properties on either
table had no effect on performance.

Documentation suggests that spatial index hints can only be used in queries
in SQL Server 2012, but surely there's a work around for this?

zoom and move map control

Thu, 03 May 2012 08:18:56 Z

Hello,

when designing a report and using the Map .Net control, you can zoom the map and move it around. But when you run the report, it will be static in both size and position. Is it possible to let the users zoom and move the map after they have rendered the report?

Spatial Data/Indexing performance issues

Tue, 01 May 2012 12:07:12 Z

Any advice on this?

http://www.sqlservercentral.com/Forums/Topic1292997-391-1.aspx

thanks,

Chris

How to convert a SQL geography column back to individual latitude and longitude?

Tue, 01 May 2012 22:11:22 Z

I have some routes stored in a sql 2008 database as geography linestrings, basically a bunch of lat\long points. I need to draw these routes on bing maps, bing needs them as individual lat\long points.

So how would I take them from the database into C# .net \ typed data set and iterate them. I can see them in the typed data set but it comes across as LINESTRING( -2.3234, 54.2323 ....). I could build a parser that takes the dataset LINESTRING and converts it into lat\long but I'd hate to reinvent the wheel if there is built in way to do it. Thanks for any help

data reader slow when performing spatial query

Mon, 30 Apr 2012 14:40:35 Z

Good afternoon,

I have an odd issue with my data : I'm trying to perform a simple bounding box query, but fetching geometry data is longer than further processing the data.

Here is my code :

using (var conn = new SqlConnection("myconnectionstring"))
 using (var cmd = new SqlCommand("SELECT geom FROM " + table, conn))
  {
    cmd.CommandType = System.Data.CommandType.Text;
    using (var rdr = cmd.ExecuteReader())
    {
       DateTime start = DateTime.Now; // start timer
       while (rdr.Read())
       { SqlGeometry geom = (SqlGeometry)rdr[0]; }

       Debug.WriteLine("loaded in: " + (DateTime.Now - start).TotalMilliseconds); // around 50ms with 36000 records
    }
  }

The fetch part (the while block) runs for 50ms.

Now, when I add a WHERE clause that includes everything and returns the same records (like WHERE geom.STIntersects(@TheWholeWorld) = 1), the same block takes 500ms (ten times slower).

using (var conn = new SqlConnection("myconnectionstring"))
 using (var cmd = new SqlCommand("SELECT geom FROM " + table + " WHERE geom.STIntersects(@worldBBox) = 1", conn))
  {
    cmd.CommandType = System.Data.CommandType.Text;
    using (var rdr = cmd.ExecuteReader())
    {
       DateTime start = DateTime.Now; // start timer
       while (rdr.Read())
       { SqlGeometry geom = (SqlGeometry)rdr[0]; }

       Debug.WriteLine("loaded in: " + (DateTime.Now - start).TotalMilliseconds); // now 500ms (!) with the same 36000 records
    }
  }

Do you know why this is happening ?

The aim is generating image tiles on the fly the .net way, and with this issue, on tiles with a lot of geometries, 75% of processing time is passed fetching data.

STUnion Resulting in huge number of vertices

Mon, 30 Apr 2012 14:51:37 Z

Hello,

I am trying to merge to polygons using STUnion method. The resulting polygon seems to have a vertex where huge number of vertices are stacked over each other. Here is the code I am using to merge-

DECLARE @Polygon1 geometry  
SET @Polygon1 = geometry::STPolyFromText('POLYGON ((-10553853.400672335 4002307.7175941337, -10553858.803142548 4002348.6219978333, -10553912.122381078 4002360.8854242279, -10553931.955673218 4002310.851436615, -10553853.400672335 4002307.7175941337))',3857)

DECLARE @Polygon2 geometry  
SET @Polygon2 = geometry::STPolyFromText('POLYGON ((-10553858.204683276 4002344.09157344, -10553835.215061188 4002355.7880477905, -10553899.708805084 4002368.6270828247, -10553900.671458066 4002358.2518135007, -10553858.803119659 4002348.6220970154, -10553858.803115845 4002348.6220932007, -10553858.80311203 4002348.6220932007, -10553858.803108215 4002348.6220932007, -10553858.8031044 4002348.622089386, -10553858.803100586 4002348.622089386, -10553858.803096771 4002348.622089386, -10553858.803092957 4002348.6220855713, -10553858.803089142 4002348.6220855713, -10553858.803089142 4002348.6220817566, -10553858.803085327 4002348.6220817566, -10553858.803081512 4002348.6220779419, -10553858.803077698 4002348.6220741272, -10553858.803073883 4002348.6220741272, -10553858.803073883 4002348.6220703125, -10553858.803070068 4002348.6220664978, -10553858.803066254 4002348.6220664978, -10553858.803066254 4002348.6220626831, -10553858.803062439 4002348.6220588684, -10553858.803062439 4002348.6220550537, -10553858.803058624 4002348.6220550537, -10553858.80305481 400
2348.622051239, -10553858.80305481 4002348.6220474243, -10553858.803050995 4002348.6220397949, -10553858.80304718 4002348.6220321655, -10553858.80304718 4002348.6220283508, -10553858.803043365 4002348.6220207214, -10553858.803043365 4002348.622013092, -10553858.204683276 4002344.09157344))',3857)





  var firstStandGeom = SqlGeometry.Parse(firstStandDTO.StandAreaPolygonWKT);
            var secondStandGeom = SqlGeometry.Parse(secondStandDTO.StandAreaPolygonWKT);
            double dist = firstStandGeom.STDistance(secondStandGeom).Value;
            double buffer = Constants.SPLIT_MIN_BUFFER;
            if (dist < Constants.SPLIT_MAX_BUFFER)
                buffer = .01;
            //buffer the two stands and merge them to avoid cases where really close polygons end up being multipolygons
            //then buffer them negatively to offset the buffer done above
            firstStandGeom = firstStandGeom.STBuffer(buffer)
                .STUnion(secondStandGeom.STBuffer(buffer))
                .STBuffer(-1 * buffer);

Any help on this will be highly appreciated.

Thanks

Apurva

Help with GeoCoding

Mon, 30 Apr 2012 12:44:53 Z

Hello,

Can anyone give me some pointers on how to geocode an address in the UK to Lat/Long using SQL2008 please?

I have had a good search around but have not been able to find any help.

Many thanks

Trev

How to add a map to the map control?

Thu, 26 Apr 2012 19:21:23 Z

Hello,

I am interesting in learning how to use the map object in SQL reporting services. Since I live in Sweden I would like to add a Swedish map. I have bought a shape file for Sweden which is supposed to contain information about the Swedish postal areas based on the first two digits in the postal code (WGS 84).

Can anyone tell me how to use these files in SQL Reporting services so that I get a map just like the one that is included by default (USA)? Or maybe point me to documentation/tutorial on the subject?

How to define an oval shape?

Sat, 28 Apr 2012 05:12:27 Z

Hi all,

I'm trying to define an oval shape that looks like this:

How can I do it using

geography::STGeomFromText('

CURVEPOLYGON(CIRCULARSTRING( ??????? )

Thanks for your help :)

What SRID and Spatial Type Should I Use for this Prj file

Thu, 26 Apr 2012 05:28:31 Z

Hi, I am trying to import Zillow neighborhood boundry shape files (http://www.zillow.com/howto/api/neighborhood-boundaries.htm) in my DB. I want to ask what data type should I use (geometry or geography) and what SRID should I Use for this

here is the prj file data

GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

thanx

problem when trying to insert a polygon in SQL Server table with MapInfo Easyloader

Wed, 25 Apr 2012 23:16:32 Z

Hi All,

I'm using SQL Server 2008 R2 SP1 and the latest version of MapInfo (11.0.4).

With a tool called EasyLoader I'm trying to insert a polygon in a table of SQL Server that contains an insert trigger. The trigger is doing a quite long geographic query finding and saving in a new table all the points from an existent table that fall inside the polygon inserted.

Everything was working fine till 1 month ago, then it stopped working without any apparent reason.

What's happening now is that EasyLoader can insert the polygon if there is no trigger or if the code inside the trigger is a fast query (such as update a field), but when the query starts to involve a long process, like the geographic one described before, after about 30-45 seconds the program stops saying that the inserting was successful when instead is not.

If I'm inserting the polygon from another table using only SQL Server management studio, the trigger works smoothly, so it's not a problem of the coding, but it seems almost that a sort of timeout during the query kicks EasyLoader out.

Any idea?

Thanks,

Elisa

Node to Node Connectivity

Thu, 26 Apr 2012 11:55:43 Z

Hi,

i have a table named Roads it has

Roadtype smallint, RoadID Bigint , StartPoint Geometry, EndPoint Geometry and Geom Geometry Witch stores the polyline Data

and now i want to Connect the Road to Road Dynamic Connection Withn the Procedure.

any help how do i Draw the road within seconds.

thanks

Out of memory in SQL Server 2012 Management Studio

Sun, 18 Mar 2012 22:16:13 Z

Hi,

I install SQL Server 2012 ( x64 bit) and i put it a very lot of spatial data (Geography data type)

then i import shapefiles into geography field and when i want to update records (774,000 record with 8,000,000 geography points!) with :

UPDATE GIS
SET GeoPos = GeoPos.MakeValid()
WHERE GeoPos.STIsValid() = 0

this take a 1h , 45 minutes !then

SQL Server Management Studio raise this error:

Msg 6532, Level 16, State 49, Line 5 .NET Framework execution was aborted by escalation policy because of out of memory. The statement has been terminated.

PC Memory : 4G

I test again this update on SQL Server 2008 R2 x64 ( with dbo.MakeValidGeographyFromText function. i get it from codeplex ) and work it without problem!

Is the bug in the SQL Server 2012 ?

Best Regards

Question About Plotting Map USA Borders

Mon, 23 Apr 2012 20:58:28 Z

I have a table with Million Points based on Latitude and Longitude. So when I run SQL Query it comes like this - its a Geo Data type plotten in points.

Now I need an OUTLINE for US MAP so that it looks more pretty visually....how can i embed a Line String for the USA Borders in the same query.

The table is really simpple: Customer_No, Long, Lat and Geography_Val (Geo data type)

Thanks

Single emisphere problem with Antarctica

Thu, 19 Apr 2012 14:48:28 Z

Hello !

I have recently donwloaded shape files from Natural Earth Data (110m scale ranks)

These are the shapes for all countries. All are importing OK except one : Antarctica.

Sql Server says (in french): Microsoft.SqlServer.Types.GLArgumentException: 24205 : l'entrée spécifiée ne représente pas d'instance géographique valide, car elle dépasse un seul hémisphère. Chaque instance géographique doit s'ajuster à un seul hémisphère. Cette erreur a souvent pour origine une mauvaise orientation de l'anneau du polygone.

This means that I broke the single emisphere rule. OK.

Now what is the good way for having it as a geography in my table ?

Break it into single emisphere parts ?
Do something I didn't think about ?
Use geometry instead ?

Other case is Russia that is crossing International Date Line...

I'm looking for best pratices for that cases.

Thank you

Xavier

Recieving error when converting Geometric data to Geographic data

Thu, 09 Feb 2012 22:11:08 Z

hi,

I need to convert from Geometric data to Geographic datatypes, because I want to use the map (original shape file of the Netherlands) in an SSRS report with Bing layer. This requires the map to be of the Mercator type. (SRID 4326).

I already have the shape data in a SQL table.

Upon conversion I recieve the following error:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": 
System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
System.FormatException: 
   at Microsoft.SqlServer.Types.GeographyValidator.ValidatePoint(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.Validator.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.ForwardingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.CoordinateReversingGeoDataSink.BeginFigure(Double x, Double y, Nullable`1 z, Nullable`1 m)
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseLineStringText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText()
   at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
   at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)

I've been going crazy on this and have already tried many suggestions like mentioned in : "Fixing Invalid Geography Data"

Any suggestions are appreciated.

thx.

Dinand

Change of geometry SRID

Wed, 11 Apr 2012 13:54:03 Z

Hi All,

I have a database with several spatial data (wgs84 X and Y values, Lambert2 X and Y values and Geometry points (example

0xB46B0000010C000000002836134100000000A8ED4141 with a SRID of 27572).

I would like to create new columns with : geometry points (same as above) and X/Y values both with a SRID of 2154 (Lambert93) ?

What would be the best method to create these columns ?

Many thanks in advance

Didou13

Converting from GPS lat-lon to Wyoming East State Plane Coordinate system

Mon, 16 Apr 2012 21:33:55 Z

I need help in getting around this problem:

I have the following coordinates in GPS Latitude and Longitude decimal and would like to convert them to Wyoming East State Planes Coordinate system with (FIPS Code 4901). I would be grateful for any suggestion/logic or code snippet in TSQL that I can modify and use to create a stored procedure.

GPSLatitude	GPSLongitude	GPS Elevation
N4409.7503	W10523.8541	1339.3
N4409.8183	W10524.0288	1428.6
N4409.1548	W10524.2987	1361.8

Generating SVG from WKT

Tue, 10 Apr 2012 13:12:04 Z

I recently generated SVG (Scalable Vector Graphics) from the WKT (Well Known Text) representation of MULTIPOLYGONs. This was done in order to view some selected zip code areas and allow for further editing in vector based graphics programs. Seeing as it was not all that difficult to achieve I thought it may be of interest to others as well. Below is an example of how it was done for the zip code areas, but I expect that not much modification would have to be made in order for it to work with other types of areas as well. It should provide a good starting point for anyone who needs to produce SVG from WKT using SQL.

---------------------------------------- Generate SVG ------------------------------------
declare @xOffset varchar(42), @yOffset varchar(42);

select	
	@xOffset = CAST(-AVG(ZipCodeArea.STCentroid().STX) as DECIMAL(19,10)),
	@yOffset = CAST(-AVG(ZipCodeArea.STCentroid().STY) as DECIMAL(19,10))
from
	MyZipCodeAreaTable
where
	left(ZipCode, 2) in ('41', '42', '43', '44');	


declare @header varchar(max) = 
'<?xml version="1.0" encoding="utf-8"?>' +
'<!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 1.1//EN" "http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd">' + 
'<svg xmlns="http://www.w3.org/2000/svg" version="1.1">' + 
'<g id="viewport" transform="translate(' + @xOffset + ', ' + @yOffset + ')">' + 
'<g stroke="black" fill-rule="evenodd">';

declare @middler varchar(max) = 
'</g>' + 
'<g font-family="sans-serif" fill="black" text-anchor="middle" text-align="center" vertical-align="middle">';

declare @footer varchar(max) = 
'</g>' + 
'</g>' + 
'</svg>';

declare @areas xml = (
select
	  ZipCode as [@id],
	  case ZipCode % 9
			when 0 then '#99CC33'
			when 1 then '#66CC33'
			when 2 then '#339900'
			when 3 then '#336600'
			when 4 then '#FF6600'
			when 5 then '#CC9900'
			when 6 then '#FFCC33'
			when 7 then '#FF9933'
			when 8 then '#FFCC66'
	  end as [@fill],
	  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ZipCodeArea.STAsText(),
	  'MULTIPOLYGON (((', 'M'),
	  ')), ((', ' Z M'),
	  '), (', ' Z M'),
	  ', ', ' L'),
	  ')))', ' Z') as [@d]
from
	  MyZipCodeAreaTable
where
	  left(ZipCode, 2) in ('41', '42', '43', '44')
for xml path ('path')
);

declare @labels xml = (
select
	  cast(ZipCodeArea.STCentroid().STX as decimal(28,12)) as [@x],
	  cast(ZipCodeArea.STCentroid().STY as decimal(28,12)) as [@y],
	  cast(cast(case 
		when sqrt(ZipCodeArea.STArea())/5 < 10 then 10
		else sqrt(ZipCodeArea.STArea())/5
	  end as int) as varchar(42)) + 'pt' as [@font-size],
	  cast(ZipCode as char(5))
from
	  MyZipCodeAreaTable
where
	  left(ZipCode, 2) in ('41', '42', '43', '44')
for xml path ('text')
);

select @header + cast(@areas as varchar(max)) + @middler + cast(@labels as
varchar(max)) + @footer;

Lars Rönnbäck -- http://www.anchormodeling.com -- An Agile Modeling Technique for Evolving Information

Routing using Geographic Data Types

Fri, 24 Jun 2011 13:04:01 Z

I want to use OSM data (preferably) to perform routing entirely in SQL server...

I liked the articles by Alastair Aitchison <Loading OSM to SQL> and <Loading OSM part-2> and the one which was looking for.. <Categorising and Route-finding>

I also found old forum posts which indicated, that Route calculation was not done by many people when the geography data type was introduced .. and SQL Server didn't have the functionality at that time... (Thats what I got from my research about it)...

I am also aware of "ShortestLineTo" function.... Have not tried it yet .....

What I want to do is, based on my own weights given to the 'ways' (edges/roads/streets etc) of the network (Imported from OSM file), I want to perform routing, and wish to have 3-4 routes for each source-destination couples... I have my own Visualization Software, to display vehicles running around on the map..... The input to my Visualization Software would be a text file which has set of routes calculated by SQL.

Routing is not the main purpose of my work, so I would rather use any pre-existing APIs/Engines/SQL-AddOns than write my own routing code.

If anyone who has implemented routing on SQL using OSM and kept as Open Source, I would be really glad to use it. I assure of proper acknowledgement to the coder.

I hope I m clear of what I intend to do.

Thanks a lot in advance.

Sanket Gupte

Find closest point to location

Fri, 13 Apr 2012 11:27:36 Z

Hi Folks,

I need help to optimize my algorithm to calculate the distance the nearest coast (within a range of 2km) to a given real estate location.

I have a table - t1- containing the real estates and locations and another table - t2- containing coast lines and the 'STBuffer'ed value of the coast line called Coast2000. There is 1.2 mill records in table t1 and 1600 records in table t2 with spatial index on Coast2000 using the high level grid setting.

I am using the following sql:

select a.ID, min(a.Location.STDistance(b.Coast_Location)) CoastDistance
     from t1 a
     join t2 b
          on b.Coast2000.Filter(a.Location)=1 with(index(spix_buffer))
    group by a.ID

However, this takes ages to run. Any ideas how to optimize this?

Create ConvexHull polygon of polygons in table

Sun, 08 Apr 2012 10:27:11 Z

Hi,

I have table that represent polygons and I have to create ConvexHull polygon. The problem is how to pick up all polygons from table into one geometry and then create a ConvexHull polygon!

Thanks!

Find angle in clockwise direction

Mon, 02 Apr 2012 07:00:30 Z

I have 2 lines connected, represented by 3 points(LAT, LONG), and I want to find the angle between them. But not just the regular angle, I want to find the angle always in a CLOCKWISE motion.

please suggest.

Thanks ..

Mayukh Roy

Best way of displaying multiple polygons together?

Wed, 04 Apr 2012 05:43:52 Z

Help Please,

I have a geometry data stored in Table W. What is the best way of showing all 400 multi polygons in sql server 2008r2 by the use of a query instead of manually loading the coordinates and displaying the polygons one by one? Thanks:

I have a sample bellow:

PK	Xmax	Xmin	Ymax	Ymin	Bottom_Left	Upper_Left	Upper_Right	Lower_Right
1	134084	132893	388276	386410	POINT (132893 386410)	POINT (132893 388276)	POINT (134084 388276)	POINT (134084 386410)
2	133943	133364	388200	386746	POINT (133364 386746)	POINT (133364 388200)	POINT (133943 388200)	POINT (133943 386746)
3	133112	133079	386791	386756	POINT (133079 386756)	POINT (133079 386791)	POINT (133112 386791)	POINT (133112 386756)

SDE to STGeometry

Sun, 01 Apr 2012 23:48:27 Z

From a thread I posted to Esri Forum, trying to get some additional perspective. Obviously not specific to SQL ST, but nonetheless:

#################

I'll spare you the details, but I need to move an SDE Featureclass to SQL Spatial Type.

For various reasons I can't use Shape2SQL, nor a python script that I have to read/write geometries.

I need to move it using SSMS from my SDE DB to my plain ole regular SQL DB.

Found some stuff online that said the following should work, but I get the following error:

Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type geometry to varbinary is not allowed. Use the CONVERT function to run this query.

declare @g varbinary(max);
set @g = 0x3200000001000000A6D0E5BEAA0681AFEFC0D713D4F705ECD5F30497E222BA049DB30597B1BB04B080B704ADEA

018C3DA8F937DCDCD904FAC401;

declare @h geometry;
set @h = geometry::STGeomFromWKB(convert(geometry, @g), 3857)

select @h FEATURE_SHAPE

Or the same implementation on the table directly, acting upon the same geometry as above:

SELECT
geometry::STGeomFromWKB(convert(geometry, points), 3857) as FEATURE_SHAPE
FROM [SDE_WORK_GIS].[sde].[f216]
where fid = 1

Other methods result in errors indicating that the varbinary format is invalid.

Read/writing to datatype geometry using python results in the following geometry binary, completely different (kind of expect that since the datatypes are different):

0x110F0000010407000000AEB662F3667861C1091B9EE6B5464B418A8EE48E677861C10F0BB5CEB4454B41228E75FF

637861C15AF5B9D2B4454B41637FD971637861C13EE8D9DC9E464B4153962162297861C152B81E9D9F464B419A999

955297861C1D656EC87B6464B41AEB662F3667861C1091B9EE6B5464B4101000000020000000001000000FFFFFFFF0

000000003

Recently back from the Dev Summit where numerous Product Engineers and the like said this could be done, and that SDE stores geometry in the same format as SQL ST.

Thoughts?

Thanks in advance.

SQL Server 2008/2012 - Polygon with some near points covers the whole world

Sat, 31 Mar 2012 23:55:52 Z

Hi!

I´m creating polygons out of a collection of points, but i have a weird situation in which the SQL Server return me the enitre World :-):

This Query:

SELECT geography::STPolyFromText('POLYGON ((9.6024305 47.58411, 9.5901797 47.5720098, 9.550566 47.5371757, 9.5319082 47.5357151, 9.5146841 47.5369248, 9.502768 47.5472144, 9.6024305 47.58411))', 4326)

The Management Studio 2008 shows nothing and isn´t able to render it, the Management Studio 2012 will show you a rect from -90 to +90 and from -180 to +180 :-).

Does anybody know what the error is ?

Retrieve all coordinates that lie a certain distance from a linestring.

Fri, 30 Mar 2012 21:43:57 Z

I need help in getting around this problem.

I have two tables that have GPS coordinate locations (X, Y, Z in separate columns as well as a geometry column, GP) known as TableA. The second TableB also have separe columns (Xs,Ys,Zs , Xe,Ye,Ze as the start and finish points of traverses and then a geometry column GL, of linestrings formed from Xs,Ys,Zs , Xe,Ye,Ze). I can display the geometry as well as linestrings in sqlserver 2008r2 spatial results.
My challenge is:

1.Bringing these two geometry points and linestrings together as one display when a query is run.
2.Get a logic/algorithm to retrieve all points that lie within a certain distance (for example x meters from the red linestring) so that they can be shown in both spatial results as well as in the business Intelligence environment of ss 2008r2. Below is a sketch of the problem: I appreciate any suggestion.

Improve Accuracy of a Geographical POLYGON

Thu, 29 Mar 2012 14:31:15 Z

Hi All,

The situation is like:

We have marked an area(POLYGON) with few points.{Say, it is POLYGON1}

Later, user wanted to mark the land/ POLYGON again with much more points.{Say, it is POLYGON2}

Now, we need to improve the accuracy of the polygon using all the points. Can we merge these two Polygons somehow to improve the accuracy?

[The main problem I am facing is while creating a new polygon with all the points(lat,long) used in POLYGON1 and POLYGON2. Can't get the proper Ordering to find the polygon. We can't neglect the POLYGON1. How to deal with?]

Any suggestion is appretiated. Thanks a lot in advance.

Mayukh Roy

How to Calculate Angle Between 3 Geographical POINTs

Wed, 28 Mar 2012 11:58:57 Z

Hi All,

I want to know, can we calculate angle between 3 Geographical points.

Suppose A(-122.358, 47.653), B(-122.358, 47.753) and C(-121.358 47.653) are 3 points, and we need Angle(ABC).

Do we have any Geography functions for it?

THanks in advance..

How to derive a new GPS point that is a given direction and distance from an origin GPS point

Wed, 18 Jan 2012 23:35:13 Z

How can I use the spatial features of SQL Server 2008 to take one GPS point and derive a new GPS point that is a specified distance and direction from that point? eg given a start long/lat location - I want to obtain an end long/lat location that is 1000m due east.

Can this be done using the geography/geometry methods?

Out of Memory when using STBuffer() on complex aggregated polygons

Mon, 30 Jan 2012 15:38:58 Z

Hi chaps,

I am experiencing an out of memory issue when processing complex shapes that consist of smaller aggregated polygons. Firstly I will tell you how I got here.

I have a dataset that contains SQL geography polygons for every postcode in the UK. My goal is to create aggregate shapes for every possible postcode sector, these aggregate shapes need to load quickly so mustn't contain too many points (performance testing I have carried out show acceptable performance with shapes that contain 250 points or less). Note that a postcode sector consists of the outcode + the first digit of the incode, e.g. the postcodes G53 7WQ, G53 7NL and G53 7ZD belong to the G53 7 postcode sector.

I calculated all possible sectors and used STUnion() in a cursor to create aggregate shapes for all postcode polygons that belong to that sector. I was left with some very complex postcode sector shapes, so I decided to reduce the complexity of these shapes using the Reduce() function.

It is important that the reduced shapes do not lose coverage of any areas, or that any gaps appear between sectors, so I decided to use the STBuffer() function to buffer out the shape before reducing it (it is not a problem if a shape is a little too big, but it is a problem the other way around). I buffered them out using the following script:

SET BufferedShape = Shape.STBuffer((SQRT(shape.STArea()))/100)

The idea behind this is that the amount I buffer out is related to the area of the shape, so for example if I used the above script on a square that was 100m by 100m then I would buffer out by 1m (the area is 100 x 100 = 10000m2, square root of 10000 is 100, divide by 100 = 1).

Unfortunately it is at this stage I come across the out of memory error. It only occurs when attempting to STBuffer() these very complex aggregate shapes, I have read that that this memory issue can occur when a buffer larger than the radius is supplied.

I have tried to divide the buffer task into smaller chunks and process and commit these chunks one at a time so to reduce the pressure on the VAS memory, but I have found that this doesn't make a difference as buffering a single complex geography can eat all the memory on my machine (12GBs).

Could this be a problem specifically to do with multipolygons and buffering out some of the smaller polygons based on the STArea() of the geography value?

Has anyone else come across this problem? If so what method did you use to tackle it? Even if you have not experienced this problem, does anyone have any suggestions on how to deal with it?

Many thanks and apologies for the long post.

Richard

Simple Spatial query

Wed, 21 Mar 2012 11:30:02 Z

Probably a simple question, but I'm just starting to 'play' with spatial data, and can't get my head round this.

Given the example data

DECLARE @Towns TABLE
    (
      name VARCHAR(255) ,
      location GEOGRAPHY 
    );
INSERT INTO @Towns VALUES

('Leeds', 'POINT(-1.58333 53.8)'),
('Preston', 'POINT(-2.7166667 53.766667)'),
('Altrincham', 'POINT(-2.35 53.383333)')

SELECT towns.name ,
       towns.location
FROM @Towns AS towns

How do I 'draw' a line between each point.

I know I can hard code a line such as

geography::STGeomFromText('LINESTRING(-1.58333 53.8, -2.7166667 53.766667)',4326)

geometry::STGeomFromText('LINESTRING(-1.58333 53.8, -2.7166667 53.766667)',0)

But I thought I could read the data from the table (self joined) to get the data needed to draw the lines.

AsGml() to GeoRSS

Tue, 01 Apr 2008 20:39:51 Z

Does anybody have an elegant way of taking the GML representation of a geometry object from the AsGml() method and turn it into a valid GeoRSS markup which can be consumed by, say Virtual Earth?

My current (very inelegant) method is as follows:

1.) Define a new variable @GeoRSS (xml datatype)

Code Snippet

DECLARE @GeoRSS xml

2.) Retrieve the GML representation of objects and store them in the variable using the SELECT ... FOR XML construct

Code Snippet

SET @GeoRSS = (SELECT 'title' AS title, 'description' as description, RadarGeography.AsGml() AS [where] FROM RadarStations FOR XML PATH('entry'), ROOT('feed'))

3.) Use xquery modify method to insert <title>, <author> etc. elements at the beginning of the feed

Code Snippet

SET @GeoRSS.modify('insert <title>SQL Server GeoRSS Feed</title> as first into (/feed)[1]')

4.) So far so good. Now here lies the problem.... the values returned by the AsGml() method are:

Code Snippet

Whereas GeoRSS expects:

Code Snippet

<gml:Point>
<gml:pos>45.256 -71.92</gml:pos>
</gml:Point>

So basically for every child element of the <where> element (the alias I gave to the AsGml() column), I need to append the namespace gml: prefix - other than that the structure is identical.... the options I've considered are:
a.) Using xquery, but as far as I know while xquery can update the values of xml elements, it can't change the element names themselves.
b.) I could apply an XSL transformation, but to do that in SQL Server I would have to compile a .Net CLR assembly and I want to do this using tools that come 'out-of-the-box' with SQL Server alone.
c.) The answer may lie in the options of the SELECT ... FOR XML options, (like using the EXPLICIT switch) but I couldn't see how.

At the moment, I'm solving this by casting my (well-formed) xml statement into a varchar(max), then using a T-SQL REPLACE statement to update the <Point>, <Polygon>, <posList> element names etc. to include the <gml: prefix, before casting the whole thing back as xml again. It works, but.... Yuck!

Any suggestions would be much appreciated!

(For those not familiar, the schema of the GeoRSS GML profile can be found here - http://www.georss.org/xml/1.1/gmlgeorss.xsd)

A sample GeoRSS feed of a point object would be as follows:

Code Snippet

<?xml version="1.0" encoding="utf-8"?>
<feed xmlns="http://www.w3.org/2005/Atom"
      xmlns:georss="http://www.georss.org/georss"
      xmlns:gml="http://www.opengis.net/gml">
   <title>Earthquakes</title>
   <subtitle>International earthquake observation labs</subtitle>
   <link href="http://example.org/"/>
   <updated>2005-12-13T18:30:02Z</updated>
   <author>
      <name>Dr. Thaddeus Remor</name>
      <email>[email protected]</email>
   </author>
   <id>urn:uuid:60a76c80-d399-11d9-b93C-0003939e0af6</id>
   <entry>
      <title>M 3.2, Mona Passage</title>
      <link href="http://example.org/2005/09/09/atom01"/>
      <id>urn:uuid:1225c695-cfb8-4ebb-aaaa-80da344efa6a</id>
      <updated>2005-08-17T07:02:32Z</updated>
      <summary>We just had a big one.</summary>
      <georss:where>
         <gml:Point>
            <gml:pos>45.256 -71.92</gml:pos>
         </gml:Point>
      </georss:where>
   </entry>
</feed>

How to STUnion a STIntersect

Fri, 16 Mar 2012 02:44:31 Z

Please excuse if this has been covered elsewhere but I'm relatively new to Spatial elements within SQL Server.

I'm looking to create a proc in SQL server that when passed a simple polygon, will check a table to see if the new polygon intersects any existing polygons in that table. If it does, Union the polygon to the existing ones. If no intersect exists add the original polygon to the table as a new record.

I think it sounds simple but am struggling to find a solution.

Many thanks for any help.

WJRH

convert AsGML() into Georss

Fri, 18 Mar 2011 15:19:10 Z

Hi I saw this post. i need the same thing but its urgent if someone can give me a basic structure i can modify it for my case.

http://social.msdn.microsoft.com/Forums/en-US/sqlspatial/thread/0918974c-0b8a-4637-937a-995ef3356ba8

Just did not know how to use some of the contructs like for loop inside a stroed procedure to do the following thing

for $child in /where

if (fn:local-name($child) = "Point")
then <gml:Point> { $child/* } </gml:Point>

else if (fn:local-name($child) = "LineString")
then <gml:LineString> { $child/* } </gml:LineString>

etc.

but if i can see a sample of working code i can easily learn the xquery and its structures

Thanks

If you guys have sample code please post :)

Chamy07

Splitting/Tiling large numbers of Geography objects

Tue, 13 Mar 2012 17:21:18 Z

We have a mapping application which is required to show shapes covering many areas worldwide - districts, in some countries, boroughs in others, etc. Entire countries can be covered.

We can't show all shapes at one time, for obvious performance reasons, so we have implemented what should be a nice tiling system, utilising the benefits of http caching. Shapes are received in ESRI shapefiles and are imported using a batch script which runs Shape2Sql.exe and some SQL scripts. We then have a web interface which allows us to view the current set of 'tiles' or 'ShapeGroups'. It offers the ability to split each of these into smaller tiles. It's this last piece which we think is causing us problems, as we appear to be missing some shapes.

Out splitting function is in C#:

public IEnumerable<Viewport> SplitGrid(int times)
        {
            if (times == 1)
            {
                var medianX = MinX + ((MaxX - MinX) / 2D);
                var medianY = MinY + ((MaxY - MinY) / 2D);
                return new[]
                {
                    new Viewport(MinX, MinY, medianX, medianY, Resolution), 
                    new Viewport(MinX, medianY, medianX, MaxY, Resolution), 
                    new Viewport(medianX, medianY, MaxX, MaxY, Resolution), 
                    new Viewport(medianX, MinY, MaxX, medianY, Resolution)
                };
            }

            return SplitGrid(1).SelectMany(_ => _.SplitGrid(times - 1));
        }

So if a tile has a lot of shapes, we'll call SplitGrid passing the Id of that tile and an int saying how many times do you want to recursively loop and keep splitting. So SplitGrid(<id>, 1) would result in 4 tiles, SplitGrid(<id>, 2) will result in 16, etc.

What we appear to be seeing is gaps appearing - and we can only surmise that it's because we need to take into account geodesic/cartesian issues? Can anyone enlighten us and explain, and also if anyone knows the correct way to do this we'd really appreciate a suggested solution.

Thanks,
Monty

How to start working with spatial data

Sun, 04 Mar 2012 08:04:34 Z

Hello,

I have a table with city, state, country ( mostly of united states) and then some count. the location columns are all varchar. I want to build a map with count as the value. I am just not sure how to get started. I read some articles on using spatial data but just got very confused. SInce I have never used spatial data before, please guide me on how to do this. Thanks.

I would be using SSRS to build my map.

How to do server side paging on data sets with spatial data?

Sun, 04 Mar 2012 18:21:07 Z

I have some code which allows me to do server side paging on my data:

DECLARE @PageSize int, @PageNumber int;
SET @PageSize = 6;
SET @PageNumber = 2;

WITH SelectedNewsItems AS
(SELECT
Title,
ROW_NUMBER () OVER (ORDER BY COUNT(*) DESC, NewsItemID) AS SelectedRowNumber
FROM NewsItems
GROUP BY
NewsItemID, Title)

SELECT * FROM SelectedNewsItems
WHERE SelectedRowNumber BETWEEN ((@PageNumber - 1) * @PageSize) + 1 AND @PageNumber * @PageSize

My problem arises when I also want to return the POINT geography data in my dataset:

WITH SelectedNewsItems AS
(SELECT
Title,
PositionPoint,
ROW_NUMBER () OVER (ORDER BY COUNT(*) DESC, NewsItemID) AS SelectedRowNumber
FROM NewsItems
GROUP BY
NewsItemID, Title, PositionPoint)

I then get the error:

"The type "geography" is not comparable. It cannot be used in the GROUP BY clause."

What shall I do to do server side paging, but also return spatial data?

rune007

Polygon from point and with certain number of sides

Thu, 01 Mar 2012 08:13:48 Z

Hi,

I need to create a polygon from point with given radius. This is my code

DECLARE @g AS GEOGRAPHY = geography::STPointFromText('POINT(50 10)', 4326);
DECLARE @h AS GEOGRAPHY = @g.STBuffer(100)

SELECT @g as 'Geo', 'Point' AS 'Labels',@g.STAsText() as 'Text'
UNION ALL
SELECT @h, 'STBuffer(100)', @h.STAsText()
SELECT @h.STNumPoints(), 'NoPoints', ''

Here are two questions:

1. Does .STBuffer(100) creates a radius of 100 meters? What does the unit comes from?

2. My polygon has 72 points ( returned by STNumPoints() ) How can I make a polygon with specific number of points/sides, for e.g. hexagon?

Thanks,