extract centre Point from polygon

Andrew Ingall 80 Reputation points
2024-05-10T12:09:45.3233333+00:00

Hello

I am trying to extract the x,y coordinates in british national grid (27700) for the centre of each polygon from the OS_NGD_LUS_FTS_SITE.SHAPE and export them as the fields x_coord and y_coord. My code is below, but not sure where to start so any advice taken.

SELECT
OS_NGD_LUS_FTS_SITE.OBJECTID, OS_NGD_LUS_FTS_SITE.osid AS LUS_OSID, OS_NGD_LUS_FTS_SITE.description, OS_NGD_LUS_FTS_SITE.name1_text,  OS_NGD_LUS_FTS_SITE.geometry_area,  OS_NGD_LUS_FTS_SITE.primaryuprn,  OS_NGD_LUS_FTS_SITE.SHAPE, LR_NSDC_LAND_OWNERSHIP.Title_Number
FROM
OS_NGD_LUS_FTS_SITE join LR_NSDC_LAND_OWNERSHIP
on OS_NGD_LUS_FTS_SITE.SHAPE.STIntersects(LR_NSDC_LAND_OWNERSHIP.SHAPE) = 1
WHERE description = 'Allotments' and primaryuprn is null
ORDER BY description ASC

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,899 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 113K Reputation points
    2024-05-10T12:22:49.0933333+00:00

    Maybe like this:

    select
       OS_NGD_LUS_FTS_SITE.SHAPE.STCentroid().STX as x_coord, 
       OS_NGD_LUS_FTS_SITE.SHAPE.STCentroid().STY as y_coord
    from . . .
    

0 additional answers

Sort by: Most helpful