Determine is gps coordinate is in territory

[bgreiman]

We are using SQL Anywhere v12.01.

We have captured a list of GPS coordinates that define a sales territory. I want to determine if a customer's coordinate will be inside that territory. Looking for some direction on how this might possibly be implemented. I am new to spatial data, is that is possible solution and how would I go about converting my list of GPS coordinates to a special data?

Thanks in advance for any guidance.

Brian

[VolkerBarth]

Do you know what format is used for your coordinate data (such as WGS 84)? If not, could you show how the data looks like for some sample cases?

Just as a quick start, I would recommend to follow the according tutorials, if you have not tried to do so:

Tutorial: Experimenting with the spatial features

There are some samples how to test for containment.

Just some hints, no more, obviously...

[bgreiman]

I think we have a pretty solid implementation started for this project.

We are capturing gps coordinates for our territory boundaries for a given territory. We load these into a table using a column of type st_polygon. Here is an example: insert into sy_territory (territory_id, territory_name, boundaries) VALUES
(10, 'Douglas County', NEW ST_Polygon('Polygon ((46.036599 -95.302208, 46.039553 -94.170480, 45.464620 -93.831735,45.374654 -95.168405, 46.036599 -95.302208))'));

We can then compare our customer table which contains a column of type st_point which hold the gps coordinate of the customer against the sy_territory table:

select customer.customer, customer_name, customer.city, customer.location, customer_in_territory = (select customer.location.st_within(boundaries) from sy_territory where territory_id = 10) from customer

[VolkerBarth]

Just to understand: This seems to be a working solution for you, so do you have any further questions on that?

FWIW, are you using any particular SRID for your region (a flat-Earth one, apparently, as ST_Within() would not work on round-Earth, AFAIK)?

[bgreiman]

No further questions. If I understand your questions, we are using WGS 84 (planar).

[VolkerBarth]

Yes, that's what I had asked for.

[Former Member]

Just a tip, if you start to see confusing results around the boundaries of your territories, you might want to consider using a locally-relevant planar SRID. You would construct the polygon as the SRID that matches your GPS (e.g. either planar or round-earth WGS-84, depending on how you want the edges to be connected), then transform to your local planar SRID. Make sure your customer location is using the same SRID.

You might also consider using ST_Intersects instead of ST_Within, as ST_Within excludes the boundary. If you use ST_Within and query a point that is also a point of the boundary, you will get 0 back. ST_Intersects also works directly on the round-Earth SRS, so you could just use 4326 if you want. ST_Intersects may, however, show a customer being in multiple territories if your boundary lines intersect/overlap.