I have 2 polygons, first polygon inside the second polygon, and i have a locations in the red points some of them in the first polygon and the other in the second, check the below image:

I need a spatial query in SQL to order the locations based on the polygons, i need the locations in the first polygon comes first then the locations in the second polygon comes last, Also I don't need the locations outside the polygons to appears.

First polygone path: [ 32.266487,36.034219 , 32.036275,35.806253 , 31.838155,36.080911 , 31.772798,36.391275 , 32.061883,36.410501]

Second polygone path: [ 32.375757,36.226544 , 32.354697,35.855691 , 32.189814,35.548074 , 31.838155,35.592019 , 31.55773,35.938089 , 31.754116,36.072671 , 31.480465,36.53135 , 31.553049,36.904886 , 31.700385,36.86918 , 31.894138,37.020242 , 32.278098,36.594522]

DB sample for the Locations in red points

CodePudding user response：

First, some setup:

use tempdb;
drop table if exists dbo.Neighborhoods;
create table dbo.Neighborhoods (
    NeighborhoodID int not null identity
        constraint PK_Neighborhood primary key clustered,
    Shape geography not null,
    IsHighValue bit not null
);

drop table if exists dbo.Locations;
create table dbo.Locations (
    LocationID int not null identity
        constraint PK_Location primary key clustered,
    Point geography not null
);
declare @a geography = geography::STPolyFromText('POLYGON((
   32.375757 36.226544, 32.354697 35.855691,
   32.189814 35.548074, 31.838155 35.592019,
   31.55773 35.938089, 31.754116 36.072671,
   31.480465 36.53135, 31.553049 36.904886,
   31.700385 36.86918, 31.894138 37.020242,
   32.278098 36.594522, 32.375757 36.226544
))', 4236);
set @a = @a.ReorientObject();

declare @b geography = geography::STPolyFromText('POLYGON((
   32.266487 36.034219, 32.036275 35.806253,
   31.838155 36.080911, 31.772798 36.391275,
   32.061883 36.410501, 32.266487 36.034219
))', 4236);
set @b = @b.ReorientObject();

insert into dbo.Neighborhoods 
    (Shape, IsHighValue)
values
    (@a, 0),
    (@b, 1);

insert into dbo.Locations
    (Point)
values
    (geography::Point(36.6, 31.7, 4236)),
    (geography::Point(36.5, 31.9, 4236)),
    (geography::Point(35.7, 32.2, 4236)),

    (geography::Point(36.2, 31.9, 4236)),
    (geography::Point(36.1, 32.1, 4236));

A couple of notes before I move on from that.

1. The format in which you specified your polygons was not immediately consumable by SQL. While the formatting I needed to apply was minor, providing this in the future will make it more likely that people are going to help (i.e. making the barrier to entry low helps us help you). That said, it might be the case that you're really new to geospatial in SQL and didn't know the right things to Google, so not a big deal.
2. Related to the above, the well-known text (WKT) for the polygons had a couple of problems.
   1. The start and end points need to be the same. That's easy enough to fix by just copy/pasting the start point to the end point
   2. The had what is called a ring orientation problem. For the geography datatype, order in which you specify points matters. In the order specified, the polygon defined a shape that was the entire globe minus a small hole. That's almost certainly not what you wanted, so a call to ReorientObject() fixes it. So would have changing the order of the points, but I'm lazy.