geo fencing

postgis_geofencing.md

Install postgis first if you do not have it yet. brew install postgis

Check if the db has created postgis extension

select * from pg_extension;

Create postgis extension

create extension if not exists postgis;

Createa a table

create table geofencing (id serial primary key, label varchar(1024), geom geometry);

Geocoding

pip install geopy

Anaconda packages

conda install -c ioos gdal proj.4 pyproj fiona shapely

Convert lat lng to geometry

update latlng_table set geom=st_point(lng, lat);
select updategeometrysrid('latlng_table', 'geom', 4326);

-- other srid operations
-- st_setsrid(geom, 4326);
-- st_transform(geom, 3857);
-- st_srid(geom);  -- return the srid
-- find_srid('public', 'latlng_table', 'geom');

create index index__latlng_table__geom on latlng_table using gist(geom);