SQL – Extract Centroid X and Centroid Y Values

By David Crowther

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.

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

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.

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.

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.

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!