How to check an AIS signal is outgoing from a geofence with spatial database?

Question

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?

Answer 1

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.