Loading data

Topics: User Forum
Oct 2, 2006 at 7:46 AM
Hi guys not sure if anyone can help me but here goes.

I am currently trying to implement your tools and am having a few problems. I have the geom tables all set up, but can not see a way to get data into the system. How do you create a WKB value based on the bounding rectangle in the geom table ? The data I am using is existing data and only defines bounding areas. I can write my own functions to create a wkb on a trigger but is this duplication of effort.

Regards

Ben.
Coordinator
Oct 2, 2006 at 3:19 PM
I'm not sure what exactly are you asking, but I will try to answer with some examples:

/* Create and populate table */
CREATE TABLE road_segments 
(fid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(64),
aliases VARCHAR(64),
num_lanes INTEGER
);
 
EXECUTE ST.AddGeometryColumn '', 'road_segments', 'centerline', -1, 'LINESTRING'; 
 
INSERT INTO road_segments (fid, name, aliases, num_lanes, centerline) 
VALUES(102, 'Route 5', NULL, 2, ST.LineFromText('LINESTRING( 0 18, 10 21, 16 23, 28 26, 44 31 )' ,-1) ); 
 
INSERT INTO road_segments (fid, name, aliases, num_lanes, centerline) 
VALUES(103, 'Route 5', 'Main Street', 4, ST.LineFromText('LINESTRING( 44 31, 56 34, 70 38 )' ,-1) ); 
 
INSERT INTO road_segments (fid, name, aliases, num_lanes, centerline) 
VALUES(104, 'Route 5', NULL, 2, ST.LineFromText('LINESTRING( 70 38, 72 48 )' ,-1) ); 
 
INSERT INTO road_segments (fid, name, aliases, num_lanes, centerline) 
VALUES(105, 'Main Street', NULL, 4, ST.LineFromText('LINESTRING( 70 38, 84 42 )' ,-1) ); 
 
INSERT INTO road_segments (fid, name, aliases, num_lanes, centerline) 
VALUES(106, 'Dirt Road by Green Forest', NULL, 1, ST.LineFromText('LINESTRING( 28 26, 28 0 )',-1) );
 
 
/* Get WKB, WKT and SRID values */ 
SELECT ST.AsBinary(centerline), ST.AsText(centerline), ST.SRID(centerline)
FROM road_segments;
 
 
/* Get table extent polygon */ 
SELECT ST.AsText(ST.EnvelopeAggregate(centerline))
FROM road_segments;

Also I suggest you to check out the OGC tests (*.sql) included in the MsSqlSpatial source code package and download the latest SharpMap source code which includes the file MsSqlSpatial.cs to see an example of how to load and use WKB data.

I hope this helps...

Best regards,
Ricardo Stuven.
Oct 3, 2006 at 9:19 AM
Thanks for the examples, I have managed to create a procedure to do this stuff. The data I am using only specifies a minimum bounding rect in lat lon
(sw corner first.) Code for procedure posted at the end of this message.

Everything seems to have come together this morning, my data works and my sharpmap stuff is working a treat. This is the first time I have worked in the geo arena and its quite a lot of fun (so far.)

Regards & Thanks


Ben.

public partial class StoredProcedures
{
Microsoft.SqlServer.Server.SqlProcedure
public static void CreateWKBValue(
SqlString schema_name,
SqlString table_name,
SqlString column_name,
SqlInt32 oid,
SqlDouble minX,
SqlDouble minY,
SqlDouble maxX,
SqlDouble maxY
)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
using (SqlCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
string schemaName = DatabaseHelper.
CheckSchemaName(command, schema_name.Value);
string tableName = table_name.Value;
string columnName = column_name.Value;

command.CommandText = string.Format(
@"UPDATE {0}.{1}
SET {2} = @ewkb
WHERE oid = @oid"
, schemaName, tableName, columnName);

command.Parameters.Add("@ewkb", SqlDbType.VarBinary);
command.Parameters.Add("@oid", SqlDbType.Int);

GisSharpBlog.NetTopologySuite.IO.WKBWriter oBinWriter = new GisSharpBlog.NetTopologySuite.IO.WKBWriter();

GisSharpBlog.NetTopologySuite.Utilities.GeometricShapeFactory oFact = new GisSharpBlog.NetTopologySuite.Utilities.GeometricShapeFactory();

oFact.

Coordinate[] points = new Coordinate[] { new Coordinate((Double)minX, (Double)minY), new Coordinate((Double)maxX, (Double)minY), new Coordinate((Double)maxX, (Double)maxY), new Coordinate((Double)minX, (Double)maxY), new Coordinate((Double)minX, (Double)minY) };

command.Parameters"@ewkb".Value = oBinWriter.Write(new Polygon(new LinearRing(points)));

command.Parameters"@oid".Value = oid.Value;

int rows = command.ExecuteNonQuery();

transaction.Commit();
SqlContext.Pipe.Send(string.Format("{0} row(s) affected.", rows));
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
};
Coordinator
Oct 4, 2006 at 5:49 AM
It's great to hear you're taking your first steps in GIS using MsSqlSpatial. Also I see you have been looking into the source code. I hope you keep having fun!

Ok, now I see clear what you wanted to do. I recommend you to use the provided function:

UPDATE geotable
SET geocolumn = ST.MakeEnvelope(minx, miny, maxx, maxy, -1)
WHERE oid = 1234;

Today the geometry binary format is compatible with WKB (but extended with SRID), however some day that could change, so do not implement your own functions assuming WKB as the storing format and do prefer the provided constructor functions ST.*FromText(), ST.*FromWKB() and ST.MakeEnvelope().

Best regards,
Ricardo Stuven.