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

asked 24 Apr '15, 18:21

bgreiman's gravatar image

bgreiman
345151624
accept rate: 0%

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...

(25 Apr '15, 05:48) Volker Barth

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

permanent link

answered 30 Apr '15, 14:45

bgreiman's gravatar image

bgreiman
345151624
accept rate: 0%

converted 01 May '15, 13:17

Volker%20Barth's gravatar image

Volker Barth
30.3k301454660

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)?

(01 May '15, 07:14) Volker Barth

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

(01 May '15, 08:15) bgreiman
Replies hidden

Yes, that's what I had asked for.

(01 May '15, 13:19) Volker Barth
1

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.

(04 May '15, 21:37) Phil Mitchell
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21

question asked: 24 Apr '15, 18:21

question was seen: 977 times

last updated: 04 May '15, 21:37