[GIS] Using M ‘Measure’ value in SQL Server 2008

geometryintersectionlinear-referencingnetsql server

MS Sql Server 2008 geometry type supports adding Z and M values to geometry objects, including linestrings. However, I cannot find any way to make use of these values, since methods such as .STIntersection() return a 2D point.

My end goal is to find where a given point intersects with a linestring and then find the equivalent Z and M values.

Googling and scouring Technet has yielded no useful information other than to make me wish I was using PostgreSql instead, which seems to include the functionality I require (unfortunately I am constrained to Sql Server).

Does anyone know how to actually make use of the Z and M values within linestrings, or of any toolsets that provide this functionality?

Best Answer

Both these values are user defined. While the Z-measure is defined as the elevation, the unit definition (feet, meters, furlongs, etc) is left up to the developer. The Measure variable isn't even defined what it means, and could be anything that's a single value.

Since neither of these are well defined, none of the built in tools can use them. Defining the tools and the meaning are left up to the developer.

From the SQL Server documentation:

The Z (elevation) value of the instance. The semantics of the elevation value are user-defined.

and

The semantics of the measure value are user-defined but generally describe the distance along a linestring. For example, the measure value could be used to keep track of mileposts along a road.

M values are not used in any calculations made by the library and will not be carried through any library calculations.

Related Solutions

[GIS] Find distance from a point to linestring

First of all you need to make sure that both your coastline features and points are in the GEOGRAPHY datatype and that the coordinates are lat/longs.

Alternatively make sure all your features are in the GEOMETRY datatype and all coordinates are projected (to Mercator, a National Grid etc.).

Then using the STDistance method you should be able to use a query similar to:

SELECT pointtable.ID,
MIN(pointtable.GEOG.STDistance(shapetable.GEOG)) as MinDistance
FROM pointtable, shapetable
GROUP BY pointtable.ID

GEOG field names should be replaced with the field names containing the GEOGRAPHY data.
ID field names should be replaced with the appropriate ID field for the table.

If you want to see the actual string represenations of a GEOGRAPHY field use SQL such as:

SELECT shapetable.ID,
shapetable.GEOG.STAsText()
FROM shapetable

[GIS] Inserting points into SQL Server using pymssql

I went ahead with my plan, as stated in Question.

For the purpose of inserting points into SQL Server, this post was very useful for me.

Here is what worked for me:

import pymssql

# connect to SQL Server instance
conn = pymssql.connect(host='localhost', user='sa', password='sa', database='nosde')

# commits every transaction automatically and setup cursor
conn.autocommit(True)
cur = conn.cursor()

# !!Chunk of code stripped out on how I get my coords, unrelated to Q.

# Store projected coords in a GEOMETRY type field
geom_type = "geometry::STPointFromText('POINT(%s %s)', 3857)" % (x, y)
    try:
        cur.execute("INSERT INTO tweets (geom) VALUES (%s)" % (geom_type))
    except TypeError:
        print "Could not INSERT", clean

    conn.close()

Best Answer

Related Solutions

[GIS] Find distance from a point to linestring

[GIS] Inserting points into SQL Server using pymssql

Related Question