sql geospatial , ado.net - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql geospatial , ado.net

Author

Topic

agismarkos
Starting Member

17 Posts

Posted - 2012-08-17 : 09:36:55

hello ,
Im writing a project in C# where i use ADO.NET to connect to my ms sql 2012 database. My database (among other things ) has two columns. the first column represent lattitude of a point and the second one represents the longitude. I use a precedure that check if two rectangles on the map intercept each the other. The column reply returns 1 value that is either 0 or 1.
edit: in this example i run it only with numbers, i plan to add variables later
my procedure works fine if i run it on sql query. But drops error if i run using ADO.net

as sql query procedure is :

DECLARE @g geography;
DECLARE @h geography;
DECLARE @s geography;

SET @g = geography::STGeomFromText('POLYGON((39.692 23.483, 23.483 39.671, 24.095 39.493, 23.466 39.800,39.692 23.483))', 4326);

SET @h = geography::STGeomFromText('POLYGON((39.800096 23.296509, 39.628961 23.128967,39.43195 23.510742 ,39.7093 23.859558,39.800096 23.296509))', 4326);
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();
SELECT @g.STIntersects(@h) as reply

/*==============================================================================================================

//------------------------ AS ADO.NET procedure is : -------------------------------
SqlDataReader rdr = null;
SqlConnection conn = new SqlConnection("Data Source=AGIS-PC;Initial Catalog=ship_maps;Integrated Security=True");// create a connection object

String commandString = @"DECLARE @g geography;
DECLARE @h geography;
DECLARE @s geography;
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();
SET @g = geography::STGeomFromText('POLYGON((39.692 23.483, 23.483 39.671, 24.095 39.493, 23.466 39.800,39.692 23.483))', 4326);
SET @h = geography::STGeomFromText('POLYGON((39.800096 23.296509, 39.628961 23.128967,39.43195 23.510742 ,39.7093 23.859558,39.800096 23.296509))', 4326);
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();
SELECT @g.STIntersects(@h) as reply";
SqlCommand cmd = new SqlCommand(commandString, conn);

try
{
// open the connection
conn.Open();
// 1. get an instance of the SqlDataReader
rdr = cmd.ExecuteReader();

while (rdr.Read())
{
// get the results of each column
string vessel_name = (string)rdr["reply"];
TextBox1.Text += " " + vessel_name;

// .....
}//while
}
finally
{
//...........
}

//=========================================================

please advice , or if this is for another topic tell me... Im beginner with sql
thanks a lot
-Agisilaos

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-17 : 09:47:28

What's the error?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 09:55:06

What is the error message, and where does it occur (in ExecuteReader, or in the statement "string vessel_name = (string)rdr["reply"];") ?

You might want to try the changes shown in red:

....
String commandString = @"SET NOCOUNT ON;
DECLARE @g geography;
DECLARE @h geography;
DECLARE @s geography;
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();
SET @g = geography::STGeomFromText('POLYGON((39.692 23.483, 23.483 39.671, 24.095 39.493, 23.466 39.800,39.692 23.483))', 4326);
SET @h = geography::STGeomFromText('POLYGON((39.800096 23.296509, 39.628961 23.128967,39.43195 23.510742 ,39.7093 23.859558,39.800096 23.296509))', 4326);
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();
SELECT @g.STIntersects(@h) as reply";
...;

agismarkos
Starting Member

17 Posts

Posted - 2012-08-17 : 10:09:43

thanks for replies
sunitabeck's solution didnt worked :(
the error is something with makevalid() i guess. I had similar error in the initial query at sql , then i insert makevalid() and it worked.
edit : it occurs at rdr = cmd.ExecuteReader();

Stack Trace:

[SqlException (0x80131904): A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.ArgumentException: 24200: The specified input does not represent a valid geography instance. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a spatial instance to shift slightly.
System.ArgumentException:
at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
.
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
Microsoft.SqlServer.Types.GLArgumentException: 24205: The specified input does not represent a valid geography instance because it exceeds a single hemisphere. Each geography instance must fit inside a single hemisphere. A common reason for this error is that a polygon has the wrong ring orientation. To create a larger than hemisphere geography instance, upgrade the version of SQL Server and change the database compatibility level to at least 110.
Microsoft.SqlServer.Types.GLArgumentException:
at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData& g, Double eccentricity, Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive(Boolean forceKatmai)
at Microsoft.SqlServer.Types.SqlGeography..ctor(GeoData g, Int32 srid)
at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type, SqlChars taggedText, Int32 srid)
.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2084358
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5096328
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2294
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +86
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteReader() +89
gmaps.Button11_Click(Object sender, EventArgs e) +185
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 10:26:40

You wouldn't happen to be running the query against a SQL 2012 database when you run it from SSMS and against a SQL 2008 database when you run it from the .Net code, would you?

agismarkos
Starting Member

17 Posts

Posted - 2012-08-17 : 10:38:25

quote:
Originally posted by sunitabeck

You wouldn't happen to be running the query against a SQL 2012 database when you run it from SSMS and against a SQL 2008 database when you run it from the .Net code, would you?

I am afraid i have messed things up with the ms sql versions... I started project in sql 2008 now i work on sql 2012 . But tbh i dont understand what you are talking about. Could you explain further please?
thanks
-agisilaos

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 11:03:08

MakeValid for geography objects and support for geography objects larger than a hemisphere are new features of SQL 2012.

The error message you see is the latter - i.e., "exceeds a single hemisphere. Each geography instance". If change your polygons and get past that, you would get error message about not being able to find MakeValid method in the CLR type.

Take a look at this page http://msdn.microsoft.com/en-us/library/cc645577.aspx and this document http://go.microsoft.com/fwlink/?LinkId=226407

agismarkos
Starting Member

17 Posts

Posted - 2012-08-17 : 15:26:32

thanks for all your help so far!
seems like the problem was the numbers not the code

I need to make some changes to the description...
I need to add these lines of code in a function but this function wont check if two polylines intercept each other. Instead of that the function will take an input of 4 points from the user, will build a polyline and after check my database what points are inside this polyline. Im completly new to functions so i could realy use some help here!
It seems like the code goes as following ... but i dont know how the g.STIntersects(@h) statement will work.
if the point is inside polyline it returns 1 else 0.

create function is_inside(@lat real ,@lng real,@lat1 real,@lng1 real,@lat2 real,@lng2 real,@lat3 real,@lng3 real,@lat4 real,@lng4 real)
RETURNS float
AS

BEGIN
DECLARE @g geography;
DECLARE @h geography;
DECLARE @s geography;

SET @g = geography::STGeomFromText('POLYGON((@lng1 @lat1,@lng2 @lat2,@lng3 @lat3,@lng4 @lat4,@lng1 @lat1))', 4326);

SET @h = geography::Point(@lng, @lat, 4326)
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();

set @s= SELECT g.STIntersects(@h) ????
return @s

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 15:50:17

My familiarity with geometry/geography is only academic, so I am not able to offer much help. Also, you need to be on SQL 2012 to use the MakeValid function on geography.

Syntax-wise, the function should be something like this, but I don't have the expertise to say whether functionally this is correct or not:

CREATE  function [dbo].[is_inside](@lat real ,@lng real,@lat1 real,@lng1 real,@lat2 real,@lng2 real,@lat3 real,@lng3 real,@lat4 real,@lng4 real)
RETURNS INT 
AS 

BEGIN 
DECLARE @g geography;
DECLARE @h geography;
DECLARE @s INT;

DECLARE @str VARCHAR(255) = 'POLYGON(('+CAST(@lng1 as varchar(32))+ ' ' + 
	CAST(@lat1 AS VARCHAR(32))+ ','+CAST(@lng2 AS VARCHAR(32))+ ' ' + CAST(@lat2 AS VARCHAR(32))+ ','+ 
	CAST(@lng3 AS VARCHAR(32))+ ' ' + CAST(@lat3 AS VARCHAR(32))+ ',' + CAST(@lng4 AS VARCHAR(32))+ ' ' + 
	CAST(@lat4 AS VARCHAR(32))+ ','+CAST(@lng1 AS VARCHAR(32))+ ' ' + CAST(@lat1 AS VARCHAR(32))+ '))'
SET @g = geography::STGeomFromText(@str, 4326);

SET @h = geography::Point(@lng, @lat, 4326)
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();

SET @s=  @g.STIntersects(@h)
return @s
END
GO

agismarkos
Starting Member

17 Posts

Posted - 2012-08-17 : 16:18:07

I run it as :

select *
from [ship_maps].[dbo].[fullweek]
where dbo.is_inside([Longitude] ,[Latitude] , 39.692394,23.483276 ,39.671256 ,24.095764, 39.484965,24.095764 ,39.493444 ,23.466797)>0;

EDIT: i found that the error is caused by the STGeomFromText('POLYGON((@lng1 @lat1,@lng2 @lat2,@lng3 @lat3,@lng4 @lat4,@lng1 @lat1))', 4326); method. It seems that it only accept numbers and not parameteres. Is there any equal method that accept parameteres?

i get the following error :

Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24141: A number is expected at position 14 of the input. The input has @lng1.
System.FormatException:
at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeDouble()
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)
.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 17:30:22

That sounds like you are using the original function you posted, without the changes I posted at 15:50:17

agismarkos
Starting Member

17 Posts

Posted - 2012-08-17 : 18:15:46

omg i just noticed the declare @str !!!
i think its time for a break :P
ah and I love you lol....
thanks a loooot!!!

ps i found and the reason of @g.MakeValid(); error...
my ms sql is 2012 but my database was version 2008 compampility level 100 .

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-18 : 08:02:11

quote:
Originally posted by agismarkos

omg i just noticed the declare @str !!!

It is not just the @str, there are a few other changes as well in the query I posted. See below - I may not even have caught all of the changes.

CREATE  function [dbo].[is_inside](@lat real ,@lng real,@lat1 real,@lng1 real,@lat2 real,@lng2 real,@lat3 real,@lng3 real,@lat4 real,@lng4 real)
RETURNS INT 
AS 

BEGIN 
DECLARE @g geography;
DECLARE @h geography;
DECLARE @s INT;

DECLARE @str VARCHAR(255) = 'POLYGON(('+CAST(@lng1 as varchar(32))+ ' ' + 
	CAST(@lat1 AS VARCHAR(32))+ ','+CAST(@lng2 AS VARCHAR(32))+ ' ' + CAST(@lat2 AS VARCHAR(32))+ ','+ 
	CAST(@lng3 AS VARCHAR(32))+ ' ' + CAST(@lat3 AS VARCHAR(32))+ ',' + CAST(@lng4 AS VARCHAR(32))+ ' ' + 
	CAST(@lat4 AS VARCHAR(32))+ ','+CAST(@lng1 AS VARCHAR(32))+ ' ' + CAST(@lat1 AS VARCHAR(32))+ '))'
SET @g = geography::STGeomFromText(@str, 4326);

SET @h = geography::Point(@lng, @lat, 4326)
SET @h =@h.MakeValid();
SET @g = @g.MakeValid();

SET @s=  @g.STIntersects(@h)
return @s
END
GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-18 : 08:05:25

Also, you are using STIntersects function with a Point. I know nothing about geography objects and their behavior but would the function return true if the point is inside the polygon and false if not? You may want to check to make sure that that indeed is the behavior.

agismarkos
Starting Member

17 Posts

Posted - 2012-08-18 : 09:43:56

sadly true. Intersect function dont seems to work that way. the Select below returns everything no matter if inside(...) equals 0 or 1.
the idea was to give inside(..) a known polygon and get the lines that have points that are inside it.

select *
from [ship_maps].[dbo].[fullweek]
where dbo.is_inside([Longitude] ,[Latitude] , 39.692394,23.483276 ,39.671256 ,24.095764, 39.484965,24.095764 ,39.493444 ,23.466797)>0;

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-18 : 10:25:30

That sort of makes sense - because on a closed surface (like the earth), you cannot really say whether a point is inside a polygon or not except in the degenerate case of the point being on one of the edges. (Similar to the problem of "Are the continents surrounded by oceans, or are the oceans surrounded by the continents?")

I obviously don't know the details of what you are trying to accomplish, but if you are trying to find whether a polygon or a linestring is intersecting the polygon in question, couldn't you construct the linestring or polygon and then test against that geography object?

agismarkos
Starting Member

17 Posts

Posted - 2012-08-18 : 10:58:55

the whole project is that i have a database with ships and their courses. and i need to find if a ships course is avery close to another ships course. Each ship has a "safe" zone around it that makes a circle . If we move that circle around then it makes a polygon. So the basic idea was to create to polygons and check if one intercept the other. It didnt work so then i changed it to find if a single point is inside the polygon. It doesnt work either the way i would like. Now i got a function that checks if a point is inside a circle. It works on sql query but when i make it a string in my C# code (for ado.net connection) it throws me error.
the other functions we described before throwed same error. It seems like i need another way to write function for ado.net
the error i get :
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'go'.
Incorrect syntax near '0.1'
error occurs at : Line 2439: rdr = cmd.ExecuteReader();

the function for the circle:

//*******************************************************************
SqlDataReader rdr = null;
SqlConnection conn = new SqlConnection("Data Source=AGIS-PC;Initial Catalog=ship_maps;Integrated Security=True");// create a connection object

String commandString = @"
create function GetDistance(
@latitudeFrom decimal(30,10),
@longitudeFrom decimal(30,10),
@latitudeTo decimal(30,10),
@longitudeTo decimal(30,10)
)
RETURNS float
AS
BEGIN

DECLARE @distance float

SET @distance = ROUND(6378.137 * ACOS(
convert(decimal(30,10),
(SIN(RADIANS(@latitudeFrom)) * SIN(RADIANS(@latitudeTo))) +
(COS(RADIANS(@latitudeFrom)) * COS(RADIANS(@latitudeTo)) *
COS(RADIANS(@longitudeTo) - RADIANS(@longitudeFrom))))), 15)

RETURN @distance
END
go

select *
from fullweek
where dbo.GetDistance(
37.96104 , 23.56677,
[ship_maps].[dbo].[fullweek].Latitude,[ship_maps].[dbo].[fullweek].Longitude) < 0.1";

SqlCommand cmd = new SqlCommand(commandString, conn);

try
{
// open the connection
conn.Open();
// 1. get an instance of the SqlDataReader
rdr = cmd.ExecuteReader();

while (rdr.Read())
{ ......... }
....
...
//***********************************************************

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-18 : 12:47:35

There are two parts to the code you posted. One that creates a function, and the second a query that uses that function. You DO NOT need to and SHOULD NOT run the first part (the part that creates the function) more than once.

The "create function" code is just that - it CREATES a function in your database. Once you do that, it is there FOREVER (unless you DROP it) for you to use. It is a SCHEMA OBJECT that is persisted in the database.

So take the code that creates the function, open an SSMS window and run it from there. After you do that, you can verify that the function indeed has been created by looking in the Object Explorer in SSMS (under Databasename -> Programmability -> Functions -> Scalar-valued functions).

After you have done that, run the query that does the calculation from an SSMS window. If you are able to get that working, you CAN get it to run from ADO.Net as well. If you run into problem with getting it to run from ADO.Net, post the code that works from SSMS and the code that you use in ADO.Net and we can make it work.

Just be sure that you are using SQL 2012 if you are using geographic objects that span more than a hemisphere or if you want to use MakeValid function on geographic objects.

agismarkos
Starting Member

17 Posts

Posted - 2012-08-18 : 21:01:48

that was the problem!!!
now everything seems to work fine !
function that finds point in polyline works too.
thank you veeery much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources