Post reply

Geography, find the common most point from 4 Lat/Long points

  • September 10, 2013 at 12:48 pm

    #279597

    I am trying to create a Function that I can pass 4 data points (Lat/Long) to and have it calculate the center-most point (lat/long). I thought of using a Polygon and using the EnvelopeCenter function, but I cannot control what order the data points are entered. The field techs make four readings and then upload the results. They take the readings from the same point.

    My sample DB is rough, but hopefully enough to give you an idea of what I am working with. I am new to the Geography data type.

    --Creating table to hold spatial data

    CREATE TABLE dbo.StreetSignSpaital (

    ID uniqueidentifier NOT NULL DEFAULT(NEWID())

    , SignUnitID varchar(25) NOT NULL

    , Point1 geography NULL

    , Point2 geography NULL

    , Point3 geography NULL

    , Point4 geography NULL

    )

    --Loading a sample record.

    INSERT INTO dbo.StreetSignSpatial(

    SignUnitID

    , Point1

    , Point2

    , Point3

    , Point4

    )

    SELECT 'E004010038DBE180'

    , geography::STGeomFromText( 'POINT(-122.561536 45.603118166)', 4326)

    , geography::STGeomFromText( 'POINT(-122.5615208333 45.6031455)', 4326)

    , geography::STGeomFromText( 'POINT(-122.56151366666 45.603131666)', 4326)

    , geography::STGeomFromText( 'POINT(-122.56154616666 45.603134333)', 4326)

    I did come across something on a Google search that I could simply take the aggregate of the Lat values and the Long values and that would be the common point, but that seems WAY too simple.

    Hope someone can help and thank you for looking.

    JJ

  • September 10, 2013 at 6:56 pm

    #1649076

    Hi

    Given that your points are likely to be fairly squarely placed an average of them will probably give you pretty good an consistent results

    Probably the best method would be to make your points into a linestring and use the EnvelopeCenter method. The order of the points doesn't appear to affect the result.

    with sample as (

    SELECT 'E004010038DBE180' ID

    , geography::STGeomFromText( 'POINT(-122.561536 45.603118166)', 4326) P1

    , geography::STGeomFromText( 'POINT(-122.5615208333 45.6031455)', 4326) P2

    , geography::STGeomFromText( 'POINT(-122.56151366666 45.603131666)', 4326) P3

    , geography::STGeomFromText( 'POINT(-122.56154616666 45.603134333)', 4326) P4

    )

    SELECT geography::STGeomFromText( 'LINESTRING(' +

    CAST(CAST(p1.Long AS DECIMAL(20,17)) AS VARCHAR(25)) + ' ' + CAST(CAST(P1.Lat AS DECIMAL(20,17)) AS VARCHAR(25)) +

    ',' + CAST(CAST(p2.Long AS DECIMAL(20,17)) AS VARCHAR(25)) + ' ' + CAST(CAST(P2.Lat AS DECIMAL(20,17)) AS VARCHAR(25)) +

    ',' + CAST(CAST(p3.Long AS DECIMAL(20,17)) AS VARCHAR(25)) + ' ' + CAST(CAST(P3.Lat AS DECIMAL(20,17)) AS VARCHAR(25)) +

    ',' + CAST(CAST(p4.Long AS DECIMAL(20,17)) AS VARCHAR(25)) + ' ' + CAST(CAST(P4.Lat AS DECIMAL(20,17)) AS VARCHAR(25)) +

    ')', 4326).EnvelopeCenter()

    FROM sample s

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply