How to use preparedstatement to update data in SQL Server column with "Geometry"

Question

I want to update the data in column of table with data type of Geometry. I have an GIS webservice that returns the JSON response with X and Y values. I have retrieved those values along with other values required for other string and Int data type column from JSON. Java code within the application is working fine but my question is how do you "update" data with data type of "Geometry".

In the database there is some sample data with in the following format "0x346C0000010CFFC7C50034B01B41BC2D52FF8F660941" . I have no clue how I can use preparestatement to set the X&Y data in this format. Any insight or advice will be helpful.

I know how to query and convert this data after doing some research which as follows but don't know what is to be done in my program.

SELECT Shape.STX as x ,Shape.STY as y from Test
WHERE ID=14082

UPDATE

"Insert INTO Test(column1, Column2, Column3, Column4, geom)
    VALUES (?, ?, ?, ?,ST_GeomFromText(?, 2100));"

pst.setString(5,"POINT("+lat+" "+log+")" );

I found this solution for now but I have no clue what "2100" means in the above query. Maybe it's wrong, I don't have a set up now but will update this post if I find any solution with the update query. In the meantime if any one has any solution feel free to update the post.

Answer 1

here is how you can insert and retrieve Spatial Data. HTH.

---

Inserting and retrieving Spatial Data in SQL Server:

Spatial Data mainly uses 2 data types:

1. Geometry - for 2d on flat surface.
2. Geography - for 3d on global / round surface.

All spatial data is stored in x-y coordinates. To handle spatial data, the DML statements use Spatial-Functions written as string (within 'quotes') as they are in WKT (Well Known Text) format, e.g. 'POINT(5 3)' (Note: WKT is a text mark-up language used to represent vector geometry objects). Following are some common functions: (For detailed explanation please refer to MSDN):

Data Insertion:

Following functions can be used in INSERT statement. e.g.

INSERT INTO Tbl1 (mySpatialColumn) Values ('LineString(-1 -2, 6 7))

• 'Point(int x int y)' : a 0-dimensional object or just a point.

• 'LineString(x1 y1, x2 y2, x.. y..)'

• 'CircularString(x1 y1, x2 y2, x.. y..)'

• 'CompoundCurve(x1 y1, x2 y2, x.. y..)'

• 'Polygon(x1 y1, x2 y2, x.. y..)'

• 'CurvePolygon(x1 y1, x2 y2, x.. y..)'

• 'MultiPoint': a collection of multiple points, e.g.: 'MultiPoint((2 3), (7 8 9.5))'

• 'MultiLineString((x1 y1, x.. y..), (x1 y1, x.. y..))': multiple lines, e.g.: 'MultiLineString((1 1, 3 5), (-5 3, -8 -2))'

• 'MultiPolygon((x1 y1, x.. y..), (x1 y1, x.. y..))': collection of multiple Polygons.

Data Retrieval:

Following functions can be used in SELECT statement. e.g.:

SELECT mySpatialCol.ToSting(), mySpatialCol.AsGml() FROM Tbl1;

• .ToString(): displays results in the same format as initially inserted i.e. as source-function with parameter values (instead of the hex values), e.g.: colName.ToString() will show: LineString(0 0, 7 8).

• .AsGml(): converts and returns values in GML/XML format (instead of returning hex or source-function), e.g. for a LineString() value - colName.AsGml() will return:

<LineString xmlns="www.example.com/gml"><posList>0 0 7 8</posList></LineString>

Note: In SSMS (Management Studio), you can view the retrieved results either as Data itself (which are hexadecimal values) or you can view the Spatial Results i.e. the shapes / diagrams (see example below):