SQL Server Spatial Forum

Why No LINQ To SQL or Entity Framework Support for Spatial Data Types??!?

Wed, 25 Nov 2009 13:01:37 Z

I have a new project I'm working on and I thought this time I would use either LINQ to SQL or Entity Framework, but then I tried connecting to my database that uses SQL Spatial Data Types and neither could access fields using the SQL Spatial Data Types. This is crazy! Now instead of using either of these two awesome tools, I need to take the long road and write my own complete data access layer.

I figured that Visual Studio 2010 / .NET 4 Beta 2 would support them, but no support their either.

When will the SQL Spatial Data Types be supported in LINQ To SQL and/or Entity Framework??

Microsoft MVP - Windows Live Platform
Blog: http://pietschsoft.com | Web.Maps.VE - ASP.NET AJAX Virtual Earth Server Control

Retriving Latitude and Longitude directely without callback with given a post code

Wed, 25 Nov 2009 05:21:41 Z

Hi All
I am using control 6.0 for bing map.
Could anyone tell me how to convert a post code into lattitude and longitude without calling callback function. Any help would be appriciate?

Thanks,
Indra Y.

FME Workbench and the invalid geography tricks

Tue, 24 Nov 2009 22:40:24 Z

Is anyone out there using FME Workbench to import geographies in one step that overcomes the "Invalid geography" problems? I have heard of people bringing the items into sql geometry format and then doing the variety of tricks to force to be valid.

It sure would be nice if there was a way to do this in a single step from within FME. Since FME seems to be able to do a ton of stuff (and can be slightly more complicated than rocket science) there may be something that can be done, but is outside of my knowledge.

SQL export to xml

Tue, 24 Nov 2009 22:49:13 Z

I am using the following code to create an xml file.

SELECT

make AS Make, model AS Model, style AS Style, trim AS Trim FROM my_data AS DATA FOR XML AUTO, ROOT('my_root')

<my_root>
<DATA Make="TOYOTA" Model="LANDCRUISER" Style="UTILITY" Trim="" />
<DATA Make="TOYOTA" Model="LANDCRUISER" Style="UTILITY" Trim="" />
<DATA Make="TOYOTA" Model="LANDCRUISER" Style="UTILITY" Trim="" />
</my_root>

What I want to do is create an xml file with a sub root tag

<my_root>
<my_subroot>
<DATA Make="TOYOTA" Model="LANDCRUISER" Style="UTILITY" Trim="" />
<DATA Make="TOYOTA" Model="LANDCRUISER" Style="UTILITY" Trim="" />
<DATA Make="TOYOTA" Model="LANDCRUISER" Style="UTILITY" Trim="" />
</my_subroot>
</my_root>

Is there a clean way to send the output directly to a file? For example:

SELECT * FROM myTable to c:\someFile.xml

Any help would be appreciated. Thanks in advance

Small benchmark with PostGIS and MS SQL-Server Spatial

Tue, 24 Nov 2009 15:26:24 Z

We're doing a small performance benchmark with PostGIS and Microsoft
SQL-Server Spatial.

We took some Tiger data and are doing queries like shown below.

Any comments about these and/or suggestions about additional queries?

- Roland

Syntax with curly brackets as table placeholders:

1. Loading data and generating indices

SELECT *
INTO {dataset}
FROM {original dataset}
WHERE geom.STIntersects(@poly) = 1

ALTER TABLE {dataset}
ADD PRIMARY KEY CLUSTERED (ID ASC)

CREATE SPATIAL INDEX {dataset}_sidx
ON {dataset} ([geom])

2. Non-spatial selection query:

SELECT Count(*)
FROM {dataset lines} l
WHERE l.roadflg='Y'

3. Spatial query I:

a)
SELECT Count(*)
FROM {dataset points} p WITH(INDEX(geom_sidx))
WHERE p.geom.STIntersects(@poly) = 1

b)
SELECT Count(*)
FROM {dataset polygons} pg WITH(INDEX(geom_sidx))
WHERE pg.geom.STIntersects(@poly) = 1

c)
SELECT Count(*)
FROM {datset lines} l WITH(INDEX(geom_sidx))
WHERE l.geom.STIntersects(@poly) = 1

4. Spatial query II:

a)
SELECT COUNT(*)
FROM {dataset points} p WITH(INDEX(geom_sidx))
WHERE p.geom.STDistance(@point) <= x

b)
SELECT COUNT(*)
FROM {dataset polygons} pg WITH(INDEX(geom_sidx))
WHERE pg.geom.STDistance(@point) <= x

c)
SELECT COUNT(*)
FROM {dataset lines} l WITH(INDEX(geom_sidx))
WHERE l.geom.STDistance(@point) <= x

5. Join of a linestring and a polygon table

SELECT SUM(pg.geom.STLength())
FROM {dataset lines} l
JOIN {dataset polygons} pg WITH(INDEX(geom_sidx))
ON l.geom.STDistance(pg.geom) <= 10
WHERE l.railflg = 'Y'

Spatial Index not being used.....well sometimes

Wed, 18 Nov 2009 19:32:55 Z

I know there have been many post on this issue, but I can't seem to find a solid answer to the problem. We have an issue where queries coming in from ArcCatalog (ESRI application) are not using spatial indexes...most of the time. I know this is a proprietary application, but it's not an issue with the application, it's an issue with the query optimizer. I say this because sometimes those queries will use the spatial indexes, but most of the time it just chooses the clustered index. We are running SQL Server 2008 SP1, and I've seen some posts saying something about this being a known issue and that Cumulative Update 3 might fix it.

I know those spatial indexes work, because I can use the Geometry methods from queries I create in SSMS, and those indexes will get used. Also, from those queries I create I do not have to use the WITH clause. The optimizer will choose those spatial indexes every time. I know that the WITH clause can be used to force the index to be used, but because the queries coming in are from a third party application, I cannot use the WITH clause.

I have also tried freeing up the procedure cache, updating statistics, and recreating the spatial indexes, but this has done nothing. At this point, I'm at a loss on how to move forward. I have contacted ESRI about this issue, and they say it's an issue with SQL Server. At this point I have to agree with them, because of the inconsistent performance from SQL Server.

Any assistance would be appreciated.

Thanks,

Dave

Get Point of a minimum Distance

Tue, 10 Nov 2009 10:36:39 Z

Hi there,

I have a single point and a table which contains some geography polygons. Now, I can easily calculate which is the nearest polygon by using STDistance and sorting. Is there an easy way to get the connection point between the point and the polygon? I.e. when i walk the minimum distance from the point toward the polygon, where do i 'hit' the polygon?

thanks!

Polar Steregraphic Projections

Thu, 19 Nov 2009 17:39:22 Z

I have a large amount of data in SQL server using ESRI SDE. The data is stored in Polar Stereographic Coordinate systems (EPSG:3031 and EPSG:3995). I would like to shift from using SDE's data storage model which has the spatial storage in a binary format in a different table from the attribute data to using the SQL Server geography type.

But, it appears that SQL server does not support these SRIDs. I could not find anything close even in the 390 SRIDs available.

What are my options?

Support for 3D OGC Shapes planned for SQL 11?

Mon, 16 Nov 2009 16:53:43 Z

Will 3D OGC (http://www.opengeospatial.org/) geometries (POLYGONZ, TINZ, etc) be supported in the next release of SQL Server (v11)?

Thanks
Andries

Problem of extra lines appearing when apply a buffer to a polygon with 2 points very close to each other

Tue, 03 Nov 2009 17:52:58 Z

Hello,

When a apply a buffer (STBuffer or BufferWithTolerance) to a polygon which has 2 points very close to each other, there a thin extra line appearing in the resulting shape. Here is a simple example :

SELECT geography::Parse('POLYGON ((0.148179995191816 47.6541796714871, 0.147984451614527 47.6541745060093, 
0.147964192853032 47.6541716952762, 0.147946096891203 47.6541648763069, 0.147931743821736 47.6541544837423, 
0.147923079560787 47.6541417689002, 0.147920863563632 47.6541276908329, 0.147924624338343 47.654113995209, 
0.147934722145717 47.6541016335643, 0.147950297425504 47.6540920743301, 0.147969315287163 47.6540862239523, 
0.147989757526645 47.654084584297, 0.14818090965349 47.6540896671125, 0.148379543085387 47.6540780198092, 
0.148792888566824 47.6540523274557, 0.148796486858373 47.6540522602022, 0.149078805922644 47.6540456955336, 
0.149079210673302 47.6540455681842, 0.14941422307804 47.6540399941278, 0.149427379759305 47.6540406465296, 
0.149672703597952 47.6540690151693, 0.149692490982198 47.6540735712802, 0.149709322879961 47.6540819857756, 
0.149721231111283 47.6540935468119, 0.149727472854001 47.65410689077, 0.149726889420521 47.6541210511025, 
0.149720351426794 47.6541342896428, 0.1497079033212 47.6541455275075, 0.149690787134495 47.6541537083386, 
0.149671037731293 47.6541579256563, 0.149650274115975 47.6541576700538, 0.149411309184178 47.6541300958256, 
0.149083093611218 47.654135527838, 0.14880317478795 47.6541420027277, 0.148391430946737 47.6541675903095, 
0.148391031753409 47.6541675827962, 0.148188394934308 47.6541794246997, 0.148179995191816 47.6541796714871))')
.STBuffer(5)

The line length and the number of lines appearing in the buffered shape seems to depends on the distance of the points and the size of the buffer.

If I remove one of the 2 very close points, there are no problem anymore.

If I change the buffer size to 10, no problem too.

Any suggestion to solve this problem ?

Thanks in advance for your answers,

BR,

Michaël Berthoix

Itelios do Brasil - São Paulo

http://www.itelios.com.br

Performance as it relates to sp_help_spatial_geometry_index output

Thu, 12 Nov 2009 17:26:16 Z

While performance tuning queries and spatial indexes for a project, I have come across a situation where I cannot completely understand how the output from sp_help_spatial_geometry_index helps explain the performance difference between two extremely similar queries. I am hoping someone in the forums can help lift the fog...

The code for the two queries differs by one line (the code below includes both lines with comments indicating which line belongs to which query).

DECLARE @geom geometry
DECLARE @GeomCollWKT NVARCHAR(max)

SET @GeomCollWKT = (SELECT shape.STAsText() + ', '
                        FROM subset_geom
    FOR             XML PATH(''))


SET @GeomCollWKT = 'GEOMETRYCOLLECTION (' + LEFT(@GeomCollWKT,
                                             DATALENGTH(@GeomCollWKT)/2 - 2) + ')'
SET @geom = geometry::STGeomCollFromText(@GeomCollWKT, 1)

SET @geom = @geom.STUnion(@geom.STPointN(1))			-- First query, 9 seconds but with false positives
SET @geom = @geom.STUnion(@geom.STPointN(1)).STBuffer(-0.002)	-- Second query, 25 seconds but correct result set

exec sp_help_spatial_geometry_index 'BASE_GEOM','s1_idx', 1, @geom

The output from sp_help_spatial_geometry_index for both queries is below (with runtimes added):

	Query 1	Query 2
RUNTIME (sec)	9	25
Base_Table_Rows	67718	67718
Bounding_Box_xmin	404902	404902
Bounding_Box_ymin	5275715	5275715
Bounding_Box_xmax	415914	415914
Bounding_Box_ymax	5287908	5287908
Grid_Size_Level_1	64	64
Grid_Size_Level_2	64	64
Grid_Size_Level_3	64	64
Grid_Size_Level_4	64	64
Cells_Per_Object	16	16
Total_Primary_Index_Rows	578045	578045
Total_Primary_Index_Pages	1723	1723
Average_Number_Of_Index_Rows_Per_Base_Row	8	8
Total_Number_Of_ObjectCells_In_Level0_In_Index	2	2
Total_Number_Of_ObjectCells_In_Level1_In_Index	9	9
Total_Number_Of_ObjectCells_In_Level2_For_QuerySample	12	12
Total_Number_Of_ObjectCells_In_Level2_In_Index	5594	5594
Total_Number_Of_ObjectCells_In_Level3_For_QuerySample	787	787
Total_Number_Of_ObjectCells_In_Level3_In_Index	120448	120448
Total_Number_Of_ObjectCells_In_Level4_For_QuerySample	225	225
Total_Number_Of_ObjectCells_In_Level4_In_Index	451992	451992
Total_Number_Of_Interior_ObjectCells_In_Level2_For_QuerySample	12	12
Total_Number_Of_Interior_ObjectCells_In_Level2_In_Index	19	19
Total_Number_Of_Interior_ObjectCells_In_Level3_For_QuerySample	530	530
Total_Number_Of_Interior_ObjectCells_In_Level3_In_Index	3350	3350
Total_Number_Of_Interior_ObjectCells_In_Level4_For_QuerySample	131	131
Total_Number_Of_Interior_ObjectCells_In_Level4_In_Index	23216	23216
Total_Number_Of_Intersecting_ObjectCells_In_Level1_In_Index	7	7
Total_Number_Of_Intersecting_ObjectCells_In_Level2_In_Index	5268	5268
Total_Number_Of_Intersecting_ObjectCells_In_Level3_For_QuerySample	253	253
Total_Number_Of_Intersecting_ObjectCells_In_Level3_In_Index	116309	116309
Total_Number_Of_Intersecting_ObjectCells_In_Level4_For_QuerySample	11	11
Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index	387866	387866
Total_Number_Of_Border_ObjectCells_In_Level0_In_Index	2	2
Total_Number_Of_Border_ObjectCells_In_Level1_In_Index	2	2
Total_Number_Of_Border_ObjectCells_In_Level2_In_Index	307	307
Total_Number_Of_Border_ObjectCells_In_Level3_For_QuerySample	4	4
Total_Number_Of_Border_ObjectCells_In_Level3_In_Index	789	789
Total_Number_Of_Border_ObjectCells_In_Level4_For_QuerySample	83	83
Total_Number_Of_Border_ObjectCells_In_Level4_In_Index	40910	40910
Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	32.91457286	32.91457286
Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	2.763819095	2.763819095
Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	64.32160804	64.32160804
Average_Cells_Per_Object_Normalized_To_Leaf_Grid	0.005877315	0.005877315
Average_Objects_PerLeaf_GridCell	170.1457286	170.1457286
Number_Of_SRIDs_Found	1	1
Width_Of_Cell_In_Level1	172.0625	172.0625
Width_Of_Cell_In_Level2	2.688476563	2.688476563
Width_Of_Cell_In_Level3	0.042007446	0.042007446
Width_Of_Cell_In_Level4	0.000656366	0.000656366
Height_Of_Cell_In_Level1	190.515625	190.515625
Height_Of_Cell_In_Level2	2.976806641	2.976806641
Height_Of_Cell_In_Level3	0.046512604	0.046512604
Height_Of_Cell_In_Level4	0.000726759	0.000726759
Area_Of_Cell_In_Level1	2097958.063	2097958.063
Area_Of_Cell_In_Level2	32780.59473	32780.59473
Area_Of_Cell_In_Level3	512.1967926	512.1967926
Area_Of_Cell_In_Level4	8.003074884	8.003074884
CellArea_To_BoundingBoxArea_Percentage_In_Level1	1.5625	1.5625
CellArea_To_BoundingBoxArea_Percentage_In_Level2	0.024414063	0.024414063
CellArea_To_BoundingBoxArea_Percentage_In_Level3	0.00038147	0.00038147
CellArea_To_BoundingBoxArea_Percentage_In_Level4	5.96E-06	5.96E-06
Number_Of_Rows_Selected_By_Primary_Filter	7315	7315
Number_Of_Rows_Selected_By_Internal_Filter	5190	5190
Number_Of_Times_Secondary_Filter_Is_Called	2125	2125
Number_Of_Rows_Output	6654	6237
Percentage_Of_Rows_NotSelected_By_Primary_Filter	89.19784991	89.19784991
Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter	70.95010253	70.95010253
Internal_Filter_Efficiency	77.99819657	83.21308321
Primary_Filter_Efficiency	90.96377307	85.26315789

The reason for the second query (the one with the extra buffer step) is that without shrinking the overlay geometry by some very small amount, the overlays I am doing return false positives. My guess is rounding errors.

At this point, my primary concern is not with my approach but trying to understand how the results of sp_help_spatial_geometry_index relate to the performance difference I am seeing. Although I would expect the queries to have some performance difference, the gap between 9 seconds and 25 seconds seems oddly large given the similar nature of the queries and similar results from the spatial index reports.

Does the difference boil down to simply the time it takes for the database engine to remove the 400+ extra records? It is my understanding the number of calls to the secondary filter can strongly affect runtimes, but the calls to the secondary filter are the same for both queries. The only real difference in the results above is the Number_Of_Rows_Output, which of course affects the Internal_Filter_Efficiency and Primary_Filter_Efficiency.

Am I completely missing the obvious?

group by and starea

Fri, 13 Nov 2009 02:42:25 Z

I've got my trusty spatial book, a few months of experience, and I'm back with what I'm guessing is a basic question.

I've got a table with something like this (the geography is a point):

ID - geography

1 - 0x234234
2- 0x982342
2- 0x763423
3- 0x234523
3- 0x234235
3- 0x234234

What I'd like to do is something like " select ID, starea(geography) from mytable group by ID"

I'm assuming I need to make some multipoint geographies out of the the things that have 3 or more points, then do the area off of that. Just losing my mind a bit trying to figure out the syntax or where to start.

I could just haul the whole mess back into C# from a query and do it in there, but I was hoping to have a query so I didn't have to haul all my data back.

how to flatten a table of nested overlapping polygons into a table of nested non-overlapping polygons

Fri, 13 Nov 2009 09:11:54 Z

Hi

I was wondering if any one else may have solved a similar problem in the past.

I have a table of nested overlapping polygons and I would like to flatten this set of polygons into a table of nested non-overlapping polygons. The new table will look almost the same except when a larger polygon overlaps a smaller one then the difference is taken. The effect being that no two polygons will overlap and if a covered point is chosen you will be guaranteed to be in the smallest polygon covering the point in the original table.

To add some clarity here are two table definitions showing an example source table and an example destination table.

declare @g1 geography = 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))';
declare @g2 geography = 'POLYGON((1 1, 9 1, 9 9, 1 9, 1 1))';
declare @g3 geography = 'POLYGON((2 2, 8 2, 8 8, 2 8, 2 2))';

declare @h1 geography = 'POLYGON((20 0, 30 0, 30 10, 20 10, 20 0))';
declare @h2 geography = 'POLYGON((21 1, 29 1, 29 9, 21 9, 21 1))';
declare @h3 geography = 'POLYGON((22 2, 28 2, 28 8, 22 8, 22 2))';

declare @source table (id int identity, name varchar(max),bounds geography)
insert into @source values ('g1', @g1);
insert into @source values ('g2', @g2);
insert into @source values ('g3', @g3);
insert into @source values ('h1', @h1);
insert into @source values ('h2', @h2);
insert into @source values ('h3', @h3);
select * from @source

declare @target table (id int identity, name varchar(max),bounds geography)
insert into @target values ('G1', @g1.STDifference(@g2));
insert into @target values ('G2', @g2.STDifference(@g3));
insert into @target values ('G3', @g3);
insert into @target values ('H1', @h1.STDifference(@h2));
insert into @target values ('H2', @h2.STDifference(@h3));
insert into @target values ('H3', @h3);
select * from @target

I hoped I could use a recursive CTE but could not discover a good enough recursive member.

Thanks in advance
Blair

Spatial Index on a table for best performance

Wed, 11 Nov 2009 04:39:46 Z

Hi all,

I have a database table that stores polygons that represent simple bounding boxes. Ocasionaly there is a point for which i would like to find a bounding box in which case it would be the point itself.

I have created a spatial index on the table just using the normal Create Index command on the field that stored the geometry.

I was wondering if this would give me the best performance or there are other better ways to do this?

Sincerely
Dan

Dan

Subquery or not?

Tue, 10 Nov 2009 02:55:54 Z

Hi all,

SELECT     sde.FIREWARDENDISTRICTS.OBJECTID AS OID, sde.FIREWARDENDISTRICTS.NAME AS Warden,

(select r.PHONE  from sde.dbo.RURALAREAOFFICE r, sde.FIREWARDENDISTRICTS f where r.Shape.STIntersects(f.Shape) = 1 group by r.PHONE) AS Phone,

                      sde.FIREWARDENDISTRICTS.Shape

FROM         sde.RIMSFWD_V INNER JOIN

                      sde.FIREWARDENDISTRICTS ON sde.RIMSFWD_V.DISTRICT_NAME = sde.FIREWARDENDISTRICTS.NAME

WHERE		sde.RIMSFWD_V.PHONE is null

UNION ALL

SELECT     sde.FIREWARDENDISTRICTS.OBJECTID AS OID, sde.FIREWARDENDISTRICTS.NAME AS Warden, sde.RIMSFWD_V.PHONE, 

                      sde.FIREWARDENDISTRICTS.Shape

FROM         sde.RIMSFWD_V INNER JOIN

                      sde.FIREWARDENDISTRICTS ON sde.RIMSFWD_V.DISTRICT_NAME = sde.FIREWARDENDISTRICTS.NAME

WHERE		sde.RIMSFWD_V.PHONE is not null

What I'm trying to achieve is being able to include Phone numbers from my sde.dbo.RURALAREAOFFICE where they don't exist in sde.RIMSFWD_V.PHONE (only 5%). I need to do this by accessing the phone no in sde.dbo.RURALAREAOFFICE that intersect each
record in my sde.firewardendistricts where the district name in the sde.rimsfwd_v matches that in the sde.firewardendistricts and where
phone is null

I hope I've made sense?

SSIS import/export does not work with spatial types

Tue, 05 May 2009 14:11:02 Z

I want to move a copy of a single table from on database on a SQL2008 server to another database on another SQL2008 server. Using the built in import/export wizard in SQL Management Studio, it complains "Found 1 unknown column type conversion(s) You are only allowed to save the package".

Has anyone found a workaround for this?

SQL Server Spatial filter optimization

Wed, 04 Nov 2009 20:01:07 Z

Hi all,

I have a spatial table containing 3400 parcels and correctly indexed, and when I run the following query on a far over dimensioned server, the performances are barely bad.

This request takes between 1,5 and 2 seconds

SELECT F.[GEOMETRY].STAsBinary() as GEOMETRY,F. ID_TRANSAC,F.ID_ENTITY,F.ID_STRUCTURE,F.ID

FROM CARTO.PG F with (INDEX([SPATIAL_PG]) )

WHERE

[GEOMETRY].Filter(geometry::STGeomFromText('POLYGON ((242353.225061205 2093829.3626259, 442712.156613794 2093829.3626259, 442712.156613794 2212891.8626259, 242353.225061205 2212891.8626259, 242353.225061205 2093829.3626259))', 0))=1

AND

[GEOMETRY].MakeValid().STEnvelope().STIntersects(geometry::STGeomFromText('POLYGON ((242353.225061205 2093829.3626259, 442712.156613794 2093829.3626259, 442712.156613794 2212891.8626259, 242353.225061205 2212891.8626259, 242353.225061205 2093829.3626259))', 0))=1

If I remove the second part of my WHERE, [GEOMETRY].MakeValid().STEnvelope().STIntersects, I get the same results, but a bit faster (between 0,4 and 0,6 seconds).

If I remove the function [GEOMETRY].STAsBinary() in the select, the response comes even a bit faster (between 0,3 and 0,5 seconds)

Is there a way to optimize the data / indexation / DB parameters, to make the functions [GEOMETRY].MakeValid().STEnvelope().STIntersects and [GEOMETRY].STAsBinary() perfectly tuned ?

Isn't the second part of my where [GEOMETRY].MakeValid().STEnvelope().STIntersects clause supposed to improve my overall performance ?

Thanks a lot,

Geomakhs.

Support for storing arc in SQL Server 2008 R2 available????

Mon, 02 Nov 2009 15:53:01 Z

Hi,

I know there is no support in for storing arcs in SQL Server 2008. Does anybody know if it is possible in the upcoming Release 2008 R2?
Or does anybody know when this feature will be available?

Thanks for the help.

Chris

DataTable.Load(IDataReader) fails on geography column

Mon, 21 Sep 2009 18:46:15 Z

I have some code like the following that fills a DataTable using an arbitrary select command. But when the reader contains a column with geography datatype I get the exception seen below:

SqlCommand cmd = new SqlCommand(command, conn);
SqlDataReader reader = cmd.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);

Unhandled Exception: System.Data.DataException: Type 'Microsoft.SqlServer.Types.SqlGeometry, Microsoft.SqlServer.Types, Version=10.0.0.0, Cu
lture=neutral, PublicKeyToken=89845dcd8080cc91' does not implement IComparable interface. Comparison cannot be done.
   at System.Data.Common.SqlUdtStorage.CompareValueTo(Int32 recordNo1, Object value)
   at System.Data.Index.CompareRecords(Int32 record1, Int32 record2)
   at System.Data.RBTree`1.RBInsert(Int32 root_id, Int32 x_id, Int32 mainTreeNodeID, Int32 position, Boolean append)
   at System.Data.Index.InitRecords(IFilter filter)
   at System.Data.Index..ctor(DataTable table, IndexField[] indexFields, DataViewRowState recordStates, IFilter rowFilter)
   at System.Data.DataTable.GetIndex(IndexField[] indexDesc, DataViewRowState recordStates, IFilter rowFilter)
   at System.Data.DataColumn.get_SortIndex()
   at System.Data.DataColumn.IsNotAllowDBNullViolated()
   at System.Data.DataTable.EnableConstraints()
   at System.Data.DataTable.set_EnforceConstraints(Boolean value)
   at System.Data.DataTable.EndLoadData()
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, I
nt32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler)

However, if I use an SqlDataAdapter instead of an SqlDataReader then I am able to successfully fill a DataTable with my results when they contain a geography column:

SqlCommand cmd = new SqlCommand(command, conn);
DataTable table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(table);

What's the difference here? The problelm is that I already have a lot of code that passes around SqlDataReader objects, and it would be a lot of work (and testing) to replace it with SqlDataAdapter. Can I somehow fill a DataTable using a SqlDataReader when the result set contains UDT's?

KML DATA TYPES

Wed, 28 Oct 2009 18:02:06 Z

Hello Experts

Our company is starting a new project, in which the KML data is loaded in compressed format into sql server. This KML data is used by a web server to give out the required info..(which is address) to the user.

I have many questions with regards to this..

1. Is it possible to load the compressed format of the KML data into SQL Server

2. When the web server grabs the data from the sql server, how can it change the digitized data into user understandable language.

Can someone throw light on this?

Thanks

ALI DBA

Is SQL Spatial supported in Compact Framework 3.5 and Sql CE 3.5 SP1?

Mon, 26 Oct 2009 20:14:16 Z

Is SQL Spatial supported in Compact Framework 3.5 and Sql CE 3.5 SP1?

How to create SQL Geography type in C#

Fri, 23 Oct 2009 20:28:27 Z

Hello,

How do I create a geography type point using C#. I am looking for some examples, blogs etc for compact framework. I was looking at :
http://jasonfollas.com/blog/archive/2008/12/11/using-ado.net-with-sql-server-spatial-objects.aspx but there are 2 problems-

1) If I add Microsoft.SqlServer.Types in my windows mobile project as a reference and debug, the project starts deploying 20 different dlls like system.web etc onto device and the app folder becomes huge in size and device runs out of memory. Is there a CF version of this dll that I can add as a reference?

2) There is no property for UDTName for sqlceParameter

I would really appreciate if someone can help me out with this!
Thanks
Apurva

Checking if a point intersects within a 3d polygon

Fri, 23 Oct 2009 03:41:37 Z

Hello,

I have read the "Beginning Spatial with SQL server 2008" and checked on the web, but I cannot find anyway how to do the following inside the DB. So I am not sure if it can even be done.

What I am trying to find a way that for a given point(x,y,z) does it intersect/contain within a 3D polygon. I know I can do it in 2D, which I have tried and it works fine.

So the actual problem I am trying to solve is -
I want to store in the DB polygons that reperesnt layers, so each layer will have a depth and then I can say for a xyz point it will be in this layer..
Is this possible?

Any help would be greatly appretiated

Thank you
Rosemary

Polygon shading

Tue, 20 Oct 2009 05:38:03 Z

How are polygons shaded using values from other columns in table? For example, there are 4788 polygons representing census block groups, where each polygon will have a color, pattern or gradient based on one of four nominal catagories.

Thanks in advance

How do I view more than 1000 rows in spatial results pane using geometry spatial data in Sql server 2008

Sun, 18 Oct 2009 14:54:38 Z

I want to show complete spatial results. When I execute a query with spatial data, geography based spatial data returns all rows as points, but using the same data as geometry will show only up to 1000 rows. All rows are returned in the query results pane in either case.

How do I show more than 1000 rows in query spatial results pane using geometry spatial data?

Solving STUnion rounding problem

Mon, 19 Oct 2009 18:43:53 Z

Hi,

I'm trying to join 3 LineStrings that I know connect and will form a single LineString. But STUnion thinks it is a MultiLineString, since it does some rounding error when doing the operation. I'll guess is not a real bug, but an effect of complex geometry-operations?

I'm thinking of implementing my own STUnion for LineString, that simply appends points from the second LineString to the first LineString. Is there a better alternative?

Thanks in advance

/Tommy

How to Convert geometry xsd to .net class and consume via web service?

Tue, 20 Oct 2009 00:19:12 Z

I need a way to represent the objects in http://schemas.opengis.net/gml/3.1.1/base/geometryBasic0d1d.xsd (especially the pointType) in .net and be able to return them from a webservice call.

The problem is that, according to the xsd, the Point can be an array of doubles. XSD.exe generates code like below. Unfortunately the array of doubles (any array other than string) cannot be serialized in xml. I have tried the techniques from http://webservices20.blogspot.com/2009/04/opengis-with-net-20-and-wcf.html that uses an alternative xsd where the generated vb code becomes array of objects.

<System.Xml.Serialization.XmlTypeAttribute([Namespace]:="http://www.opengis.net/gml"), _

System.Xml.Serialization.XmlRootAttribute(

"pos", [Namespace]:="http://www.opengis.net/gml", IsNullable:=False)> _

Public Class DirectPositionType

Private textField() As Double

<System.Xml.Serialization.XmlTextAttribute()> _

Public Property Text() As Double()

Get

Return Me.textField

End Get

Set(ByVal value As Double())

Me.textField = value

End Set

End Property

End Class

I appreciate the help.

Point-In-Polygon Stored Procedure: Using Parameters and MIsmatched Data Types

Fri, 02 Oct 2009 20:14:32 Z

I'm trying to write a stored procedure that will accept a long/lat, locate it in a polygon layer, and return an attribute value for the polygon. I have been able to write T-SQL which does the trick, but I can get a stored procedure that will work correctly.

I've narrowed down the culprit to this inconsistency. In T-SQL, this code:

select geography::Parse('POINT(-122.362 47.533)')

gives me a different result from:

declare @longitude numeric=-122.362
declare @latitude numeric= 47.533
select geography::Parse('POINT(' + CAST(@longitude AS VARCHAR) + ' ' + CAST(@latitude AS VARCHAR) + ')')

I'm not sure how to overcome this issue. I thought that local parameters would behave well, but it looks liek CASTing isn't the answer. Do I need to do a workaround for this? Please help!

...pete..

Error 6517 when trying to create Spatial Index

Wed, 14 Oct 2009 10:22:40 Z

It said something like the domain administrator of the server cannot be created, I don't know if that is the correct translation.
Error code is 6517.

I have a table with a column which datatype is geography.
I am trying to build an index on it. I am using this to store location on earth. will convert back to WGS84 later.

I add the column to the index key column grid, and select index type as spatial.
then I go to the spatial page and set the tessellation scheme to geography grid.

I click ok and this error is encountered. Please help.

Spatial Data - finding all cities in the coridor

Tue, 13 Oct 2009 22:42:16 Z

Hi,

I have a Cities geo data table with Lat and Long columns in it. I want to find all cities which fit into the 50 miles coridor between City1 and City2 (say Seattle to LA).
What is the best way to do it? Thanks.

SQL Server 2005 linked to SQL Server 2008 with Geospatial Datatypes

Mon, 19 Jan 2009 20:11:02 Z

Hi

I am trying to create a Linked Server in SQL 2005 to a SQL 2008.

First i created the linked server using the SQL Server Manager but when i run a query the following message appears

OLE DB provider "SQLNCLI" for linked server "servergeo" returned message "Invalid character value for cast specification".
OLE DB provider "SQLNCLI" for linked server "servergeo" returned message "Invalid character value for cast specification".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "SQLNCLI" for linked server "servergeo".

I also create the linked server using the next sentences

exec sp_addlinkedserver 'servergeo', '', 'SQLNCLI', NULL, NULL, 'SERVER=192.168.1.,1434', NULL
exec sp_addlinkedsrvlogin 'servergeo', 'false', NULL, 'sa', 'xxxxxx'

also got the same message.

please somebody ???

Converting Postcode to Lat Long and Vice Versa

Fri, 09 Oct 2009 06:57:32 Z

Hello All,

There are 2 new datatypes Geometry and Geography in SQL Server 2008. Geometry is primarily for 2D applications, distance calulating, drawing polygons etc. I wanted to know how we can use Geography Datatype.

Apart from using Geography datatype in Virtual earth application are there any functions available using which we can store locations based on latitude/longitude using Geography datatype and change postcodes to lat long and vice versa.

Thanks in Advance

Rakesh

SqlSpatial: Can it be true? No LINQ!

Thu, 26 Feb 2009 07:01:17 Z

Hi. I was referred to this forum as the best place to ask this. At first I thought I was doing something wrong -- and I still may be -- but maybe I can get some tips here.

Situation: I have tables in SQL Server 2008 that store map info using standard SQL Server 2005 data types and my C# geo-app uses LINQ to access tables/data, often via an light-ORM-layer generated by Microsoft SqlMetal. Well, I've added the cool SQL spatial types to my tables, and as soon as I did.

1. SqlMetal warns me that it is dropping the fields:
"Warning : SQM1021: Unable to extract column '_Area_Polygon_UDT' of Table 'dbo.rc_Resources' from SqlServer because the column's DbType is a user-defined type (UDT)."

2. Visual Studio 2008 SP1 Object To Relational Designer / Entity Builder will no longer let me visualize/edit/include tables into the workarea because they now contain an unsupported data type.

Does anybody know how to leverage the spatial UDTs using C#/LINQ even if I have to modify or create access code by hand (in the DB or in C#).

I sure hope that I'm missing out on something :-)

Thanks!

Richard

Hubert-Associates

How to get a hemisphere with specified point as the center?

Fri, 09 Oct 2009 20:01:55 Z

Imagine looking at a round globe. You can only see one "hemisphere" and the center point would be the middle of the visible area. The other hemisphere would not be visible because it would be on the "back" of the globe relative to your observation point.

Is there a way to create a SQL2008 geography polygon object that corresponds to the "visible" side, centered on a specified lat/long?

Without SqlServer.Types in Silverlight, what is the best way to work with WKB?

Mon, 05 Oct 2009 03:25:27 Z

I am using ADO.NET Data Services, which uses Entity Framework, which in turn does not understand the spatial types. As a result, I always convert my spatial types to WKB in order to use them in the Entity Framework (they show up as binary data). However, I am now working on a Silverlight Project, and the SqlServer.Types assembly isn't in Silverlight (which I hope will be changed soon!). In the meantime, is anyone aware of another way to work with WKB data in Silverlight?

Thanks,
Erick

Please Recompile SqlServer.Types for Silverlight

Mon, 23 Mar 2009 04:22:55 Z

It would be very helpful to recompile the geo types so they can be used in Silverlight. Right now the workaround is to convert the geo types to a path geo string on the server and then send to Silverlight and decode.

Getting Geography type to work with Virtual Earth Silverlight control

Mon, 21 Sep 2009 14:54:45 Z

Not sure if this is the right forum for this but here goes:

Is there any way I can create a Silverlight Virtual Earth MapPolygon from a field i pull from geography typed column in SQL Server? I need to be able to read the string representation of the polygon. I can either use .AsTextZM() or .AsGml() NOT the binary geography format. Example String returned from SQL Server:

GEOMETRYCOLLECTION (POLYGON ((90.519439697265625 22.685829162597656, 90.68359375 22.853885650634766, 90.469917297363281 22.867288589477539, 90.519439697265625 22.685829162597656)), LINESTRING (91.40277099609375 22.498050689697266, 91.513046264648438 22.345550537109375), POLYGON ((91.027206420898438 22.083885192871094, 91.174697875976563 22.218605041503906, 91.089706420898438 22.524024963378906, 91.027206420898438 22.083885192871094)), LINESTRING (90.6107406616211 22.161661148071289, 90.517486572265625 21.988048553466797), POLYGON ((90.671371459960937 21.987216949462891, 90.878860473632813 22.436660766601563, 90.662483215332031 22.783193588256836, 90.671371459960937 21.987216949462891)), POLYGON ((91.886932373046875 21.47332763671875, 91.978317260742188 21.642772674560547, 91.845954895019531 21.704160690307617, 91.886932373046875 21.47332763671875)), POLYGON ((92.327110290527344 20.744819641113281, 92.260818481445313 21.414440155029297, 92.669342041015625 21.296981811523438, 92.278045654296875 23.71082878112793, 91.9585952758789 23.727771759033203, 91.81817626953125 23.090274810791016, 91.61151123046875 22.944578170776367, 91.42608642578125 23.261943817138672, 91.344291687011719 23.098190307617188, 91.16192626953125 23.631525039672852, 91.3819808959961 24.105134963989258, 91.882575988769531 24.151556015014648, 92.117202758789063 24.389995574951172, 92.248382568359375 24.89457893371582, 92.491630554199219 24.877510070800781, 92.03887939453125 25.1874942779541, 89.850540161132812 25.288955688476563, 89.733940124511719 26.156314849853516, 89.319908142089844 26.024829864501953, 89.070732116699219 26.38532829284668, 88.857147216796875 26.240140914916992, 88.413070678710938 26.626140594482422, 88.335586547851563 26.482997894287109, 88.523063659667969 26.3673152923584, 88.182891845703125 26.150550842285156, 88.11053466796875 25.835552215576172, 89.0086669921875 25.290275573730469, 88.4542236328125 25.188398361206055, 88.043869018554688 24.685203552246094, 88.742080688476562 24.241668701171875, 88.565963745117188 23.646661758422852, 88.786338806152344 23.492841720581055, 88.727134704589844 23.247077941894531, 88.982795715332031 23.206140518188477, 88.863105773925781 22.968257904052734, 89.063003540039063 22.115474700927734, 89.244979858398438 21.642843246459961, 89.35498046875 21.966035842895508, 89.462760925292969 21.768886566162109, 89.4747085571289 22.289163589477539, 89.528251647949219 21.990690231323242, 89.615119934082031 22.319580078125, 89.581100463867188 21.70166015625, 89.883735656738281 21.8946475982666, 90 22.48375129699707, 89.915817260742188 22.037220001220703, 90.238449096679688 22.182842254638672, 90.0233154296875 21.863468170166016, 90.269989013671875 21.846940994262695, 90.401382446289062 22.260555267333984, 90.435951232910156 22.073051452636719, 90.612480163574219 22.302776336669922, 90.424400329589844 22.77018928527832, 90.6131820678711 23.218328475952148, 90.308868408203125 23.414440155029297, 90.548042297363281 23.384302139282227, 90.593826293945313 23.597965240478516, 90.8315200805664 22.688327789306641, 91.230674743652344 22.586385726928711, 91.455825805664063 22.789997100830078, 91.6583251953125 22.554164886474609, 92.327110290527344 20.744819641113281)))

Aggregating Geography

Tue, 22 Sep 2009 05:37:24 Z

Hello!

Im trying to aggregate some geography objects (polygons).

Here`s two ways to do it:

1) T-SQL :

declare @g geography
-- just first row from the table for example
select @g = geoinfo from border where VID = 3688

select @g = @g.STUnion(b.geoinfo) from border b where b.VID < 3900

select @g

2) Creating .NET assembly and aggregate:

namespace SqlGeoTools
{
  [Serializable, StructLayout(LayoutKind.Sequential),
    SqlUserDefinedAggregate(Format.UserDefined,
    IsInvariantToDuplicates = true,
    IsInvariantToNulls = true,
    IsInvariantToOrder = true,
    IsNullIfEmpty = false,
    MaxByteSize = -1)]
  public class GeoUnion : IBinarySerialize
  {
    private SqlGeography val;
    public void Init()
    {
      val = new SqlGeography();
    }
    public void Accumulate(SqlGeography Value)
    {
      if (val == null)
        val = new SqlGeography();
      if (!Value.IsNull)
        val = val.STUnion(Value);
    }
    public void Merge(GeoUnion Group)
    {
      val = val.STUnion(Group.val);
    }
    public SqlGeography Terminate()
    {
      return val;
    }
    public void Read(System.IO.BinaryReader r)
    {
      if (val == null)
        val = new SqlGeography();
      this.val.Read(r);
    }
    public void Write(System.IO.BinaryWriter w)
    {
      if (val == null)
        val = new SqlGeography();
      val.Write(w);
    }
  }
}

with

sp_configure 'clr enabled', 1
go
reconfigure
go

create assembly SQLGeoTools from 'D:\pr\sqlGeography\SqlGeoFunctions\bin\Debug\SqlGeoFunctions.dll' go

create aggregate GeographyUnion(@geog geography) returns geography
external name SqlGeoTools.[SqlGeoTools.GeoUnion]

Anything is fine until I want to aggregate too many objects.

The script takes about two minutes to complete for 1000 geo objects.

2k objects - cant wait so long. :(

How can I improve my code / server settings / smth else ?

Query Plan ignores spatial index without a hint

Thu, 01 Oct 2009 20:20:32 Z

Hello all,
I've reviewed a number of similar questions on this forum (and learned alot!) but still would like to post my question. Despite meeting the criteria set out in Bob Beauchemin's post at http://www.sqlskills.com/BLOGS/BOBB/category/SQL-Server-Spatial.aspx#p9 , I cannot induce the query plan to grab the index without a hint.

As suggested in Bob's blog post, the sql instance is SP1, i'm using the correct syntax, the join is simple (though it does have two OUTER JOINS to reference tables). I'm passing in a geography variable, using sp_executesql, using the correct syntax for the STintersects function, and I spent a fair amount of time finding a well performing spatial index.

With an index hint, and with FORCESEEK, i can drop a query time from ~12 seconds to ~1 second. Without the hint but with FORCESEEK, it comes in at about 7 seconds.

Also I find that using sp_executesql within the stored procedure helps performance significantly.

One other intriguing thing to note: I had this query running at sub second times for a while. Then I changed the index hint to reference a different spatial index, and the performance tanked. I looked at the query plan and noted it was different. So I changed the procedure back to referencing the previous index, but the performance didn't return, and the query plan didn't return either. My guess is that for a while the query optimizer was choosing one of several plans, and the one it chose rocked. But it wasn't smart enough to keep choosing that plan, and I haven't been able to induce it to choose the better plan since. I tried freeing the proc cache, and i tried regenerating statistics. So I'm pretty sure a better plan is available, if i could coax the query optimizer to choose it.

Any thoughts? At this point, it looks like I'll have to use the index hint -- unless someone has other ideas? The only thing I haven't tried yet that I am aware of is changing from the "empty set" concept to using NULL values, also described in Bob's blog.

Thanks for any ideas.
Kevin

Kevin

Conversions numeric for spatial data

Wed, 30 Sep 2009 15:53:08 Z

Hi All,

The code that I am reviewing test whether a certain point belongs in a specific polygon. It should also return polygons that is within a certain range of the point.

I am passing in feet value. In the code it translate the feet into metre ( I am assuming. That is the unit said in the sys.spatial_reference_systems for SRID 4326).

What confuses me is the feet value that is pass in is multiply by 0.00000272. So 750 feet is convert to 750 X 0.00000272. Does anyone know where this numeric transformation is derived from?

I have polygons loaded under SRID 4326 and I need to test a certain point. All polygons that is 750 feet from this point needs to be return. Any help is much appreciated.

thanks,