• Blog posts

    Bio

    By David Crowther

    SQL___Extract_Centroid_X_and_Centroid_Y_Values_-_1.PNG

    Question:

    How can you extract the X and Y Coordinates for a table in a SQL Database?

    Answer:

    If you have a spatial table in a SQL Database, you can extract the X and Y values for the centroid of each record/object.

    For these data types, you will extract:

    • polygon table – the x and y value for the centre (Centroid) of each polygon feature
    • point table – the x and y value for the location of that point

    These x and y values can then be used for other options e.g. to place a PIN Marker in the centroid of a Polygon, or to use for label placement.

    Before you run the extract ensure that you have two columns ready to extract the values into e.g. an easting (or Longitude) and northing (or latitude) field. I would suggest you make them type = Float.

    Here is a Wards table, where we have polygon features.

    SQL___Extract_Centroid_X_and_Centroid_Y_Values_-_2.PNG

    Using the DESIGN tools in SSMS, add two new fields for the X and Y values.

    SQL___Extract_Centroid_X_and_Centroid_Y_Values_-_3.PNG

    To extract the X and Y values for the centroid of each Ward polygon,  simply run these two commands, where the tablename and geometry columns will change as needed.

    update wards set easting = ogr_geometry.STCentroid().STX

    where easting is NULL

    update wards set northing = ogr_geometry.STCentroid().STY

    where northing is NULL

    Once ran the Wards table will now store the Easting and Northing for the centroid of each polygon in the table.

    SQL___Extract_Centroid_X_and_Centroid_Y_Values_-_4.PNG

    Note – the values extracted are Easting and Northings and not Longitude and Latitude, because the source table has a BNG (27700) SRID.

    The extracted X and Y values can then be used to fix a label at the centroid location of each ward polygon.

    SQL___Extract_Centroid_X_and_Centroid_Y_Values_-_5.PNG

    You may wish to have an X and Y field that has a slight offset from the centre of a feature. So, this time we will add two new X and Y fields, specifically for offsetting the placement of a label. We will do this to a new table of School Point locations called Edubase.

    SQL___Extract_Centroid_X_and_Centroid_Y_Values_-_6.PNG

    Firstly, extract the X and Y values into these new fields using the above SQL and then apply a shift to the X and Y values by adding 5 metres to the X and removing 5 metres from the Y.

    update edubase set easting_label = easting + 5

    update edubase set northing_label = northing – 5

    Now, when you apply the new easting_label and northing_label values to your data the labels will show as offset from the actual point location!

    SQL___Extract_Centroid_X_and_Centroid_Y_Values_-_7.PNG