Walkthrough - Getting Started in Visual Studio

Topics: User Forum
Jan 25, 2007 at 10:10 PM
I have read through all the threads and every other resource I can find on this potentially great addition to SQL server. However, I can not figure out how to add/retrieve data via my Business Logic in VB.net. Could someone please provide steps on how to create spatial tables in management studio, then access those tables via .NET code to add/retrieve records for displaying within an ArcObjects Engine tool. I want to store simple polygons with attribute information in one table and gps locations in another table. Once I have those features accomplished I will dive into more complicated challenges. Thank you all for your help getting me started with this.
Coordinator
Jan 26, 2007 at 12:30 PM
The key is to get a standard Well-Known Binary (WKB) representation of the geometry and then load it using your favorite GIS library. For example, using ArcObjects from VB.NET the code should look like:

	command.CommandText = "SELECT ST.AsBinary(the_geom) FROM the_table"
	Using reader As SqlDataReader = command.ExecuteReader()
		Dim factory As IGeometryFactory = New GeometryEnvironment()
		While reader.Read()
			Dim geometry As IGeometry
			Dim length As Integer
			factory.CreateGeometryFromWkbVariant(reader.GetSqlBytes(0).Value, geometry, length)
		End While
	End Using

Updates and inserts should be made using the ST.GeomFromWKB function:

	Dim wkb As Byte()
 
	' Load wkb. I'm not sure how to do it, 
	' but there's a method called IWkb.ExportToWkb that could help.
	' I did't find related sample code at ESRI site.
	' Maybe you'll have more luck.
	' ...
 
	command.CommandText = "UPDATE the_table SET the_geom = ST.GeomFromWKB(@wkb, @srid) WHERE oid = @oid"
	command.Parameters.AddWithValue("@wkb", wkb)
	command.Parameters.AddWithValue("@srid", -1)
	command.Parameters.AddWithValue("@oid", 1234)
	command.ExecuteNonQuery()

Best regards,
Ricardo Stuven.
Coordinator
Jan 26, 2007 at 12:34 PM
BTW, give a try to SharpMap :)

Best regards,
Ricardo Stuven.
Jan 26, 2007 at 1:42 PM
Thanks, that is a big help. I have started reading about WKB's and frankly, didn't know they existed in the ESRI framework. I want to give sharp map a try, but I am not familiar with it and I am in a time crunch. I am confused by the query statement :
command.CommandText = "SELECT ST.AsBinary(thegeom) FROM thetable"

What is "the_geom"? Ideally I would like to return a single row based on an ID lookup and then parse the geom (a polygon) into the geometryfactory. Thanks again for your help. Ohh, a thought, would it be better to put the command in as a storedproc?
Coordinator
Jan 26, 2007 at 2:13 PM
> What is "the_geom"?

The column containing the geometry. It could have a different name, but this is the default when you import from a shapefile. As "oid" is the default column name of the primary key.

ST.AsBinary is the function that returns a WKB representation of the stored geometry. For humans, there's also the ST.AsText function which returns Well-Known Text (WKT).

> Ideally I would like to return a single row based on an ID lookup

 
	command.CommandText = "SELECT ST.AsBinary(the_geom) FROM the_table WHERE oid = @oid"
	command.Parameters.AddWithValue("@oid", 1234)
	Using reader As SqlDataReader = command.ExecuteReader()
		If reader.Read() Then
			...


> Ohh, a thought, would it be better to put the command in as a storedproc?

I don't discuss religious matters :-)
Well, I'd only say it depends on your judgment and the context of your application.

Best regards,
Ricardo Stuven.
Jan 26, 2007 at 2:22 PM
Ricardo, your very timely responses are greatly appreciated. You've been extremely helpful thus far and forgiving of my lack of knowledge. I am trying to create a table in sqlexpress that contains geometry but I do not want to import a shape file. How do you add a field to an existing table that is spatial in nature? I get over this hump and I imagine I'll be smooth sailing.

Thanks as always,
Tom
Coordinator
Jan 26, 2007 at 2:59 PM
See the second post at Loading data

Besr regards,
Ricardo Stuven.
Jan 26, 2007 at 3:02 PM
Thanks again. To prove I can actually figure some of this out on my own, I rad through the OpenGIS Spec and dived into the added tables and stored procs. For those who are trying to do this in the future, below is how I added a polygon geometry column to an existing table:

DECLARE @schema_name nvarchar(4000)
DECLARE @table_name nvarchar(4000)
DECLARE @column_name nvarchar(4000)
DECLARE @srid int
DECLARE @type nvarchar(4000)

-- TODO: Set parameter values here.
Set @schema_name = 'dbo'
set @table_name = 'Scenes'
set @columnname = 'thegeom'
set @srid = 4629 --4629 is NAD83 spheroid
set @type = 'POLYGON'

EXECUTE CoBRAGIS.ST.AddGeometryColumn
@schema_name
,@table_name
,@column_name
,@srid
,@type

Thanks again for your help Ricardo!
Coordinator
Jan 26, 2007 at 7:24 PM
You're welcome.
When you get "smoothly sailing" these waters, I'll be thankful if you share with us the actual implementation of the ArcObjects geometry to WKB conversion (using "ExportToWkb" or something else).

Best regards,
Ricardo Stuven.
Jan 29, 2007 at 3:28 PM
In order to create a Wkb Variant, you need to use the iGeometryFactory3 interface. The code is as follows :

Dim geoFactory As IGeometryFactory3 = New GeometryEnvironment

'clone geometry object
Dim clone As IClone
clone = geometry

Dim localGeom As IGeometry

localGeom = clone.Clone


Dim outWkb As Object = geoFactory.CreateWkbVariantFromGeometry(localGeom)


However, I am not sucessful inserting a new row into my table. My table contains 4 columns that my insert statement does not address; thegeomEnvelopeminX and maxX, and thegeomEnvelopeminY and maxY. I am speculating that my insert statement (in a stored proc) is failing because I have no data for those fields. Any thoughts on how to sucessfully insert a new row into my database?
Jan 29, 2007 at 4:57 PM
After still further investigation, I have found the SQL error. It is "The INSERT statement conflicted with the CHECK constraint "enforcetypeScenesthegeom""

Below is my stored proc :

ALTER PROCEDURE dbo.CreateScene
-- Add the parameters for the stored procedure here
@sceneName varchar = 0,
@sceneDescription varchar = 0,
@wkb varbinary(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @srid int
SET @srid = 4629

-- Insert statements for procedure here
INSERT INTO dbo.Scenes (SceneName, SceneDescription, the_geom)
VALUES (@sceneName, @sceneDescription, ST.GeomFromWKB(@wkb, @srid))
END


I can't figure out what the constraint is on the_geom column.


Coordinator
Jan 29, 2007 at 8:20 PM
> My table contains 4 columns that my insert statement does not address;

Ignore them. Those columns the_geom_Envelope_* are used for indexing purposes only and are updated automatically by a trigger.

> I have found the SQL error.
> It is "The INSERT statement conflicted with
> the CHECK constraint "enforce_type_Scenes_the_geom"

The type of the geometry you are trying to insert is different to that you specified when you imported the data or when you ran ST.AddGeometryColumn. If you are not sure what geometry you'll be using, better use 'GEOMETRY' (instead of, for example, 'POLYGON').

You can remove this restriction dropping the constraint:
ALTER TABLE dbo.Scenes 
DROP CONSTRAINT enforce_type_Scenes_the_geom

Best regards,
Ricardo Stuven.