0

I have tens of thousands of geofences on the AWS Redshift table A.

And also logging of tens of thousands of AIS signals every 1 hour on table B.

However, I want to check and log that which AIS is incoming or outgoing for whole geofences.

How can I achieve that?

YkPark
  • 7
  • 1

1 Answers1

0

From your description of the problem, I would guess that a geofence needs to be transformed into a polygon using ST_Polygon(). Once you have your polygons (table A), you will need to take the AIS signals and transform them into geolocation points (table B) using ST_Point().

When you have all your data transformed into compatible geolocation format - you will use the ST_Contains().

I will add that geolocation data might take sometime in Redshift and you will most likely have to do a cartesian product of all your polygons vs all your geolocation points.

I hope I understood your problem properly.

beatbox
  • 21
  • 1
  • 5
  • Actually, I have used ST_Contains() function to check incoming. but I wonder how to check outgoing signals from inside to the outside of geofence. Besides, as you mentioned in the reply, the cartesian product query is too heavy. Therefore, I'm looking for a solution. – YkPark Oct 18 '21 at 09:16
  • How is your table B looking? ie do you have a source GPS location and a target GPS location? Is there some indication of direction? – beatbox Oct 18 '21 at 09:19
  • Table B has fields such as latitude, longitude, heading, and there is no coordinates for the target location. – YkPark Oct 25 '21 at 08:40