Citation: Emma Zgonena, Renato Molina, Juan Carlos Villaseñor-Derbez (2025). Mexican Ports [gis dataset]. gitHub. https://doi.org/10.5281/zenodo.15778574. last updated: 2025-09-09 (update frequency: As Needed). Abstract: A geospatial dataset of the major ports of Mexico, some of which are included in CONAPESCA’s landing data. It is intended to interface with other data in mex_fisheries, such as vessel- and port-level landings information. Key words: Mexico, Ports, Fisheries Open Geospatial Consortium Standards: dynamic vector tiles | | WMS (coming soon) | WFS (coming soon) Analytical Functions: gdsc_nearest_neighbor(table=mx_ports,lat=25.718,lng=-80.279,id=port_name) ETL documentation

ETL for mx_ports

######### # GDSC ETL is performed in two steps: shell script to ETL data into postgres # and then additional transformations with SQL in postGIS (if needed). #### ######### # Step 1 - bash script (pseudo code) #### # create directory structure and move into it mkdir -p /data/mx_ports/{download,etl} && cd /data/mx_ports # set update flag based on last update and update frequency do_update=0 if date() < last_update + update_frequency else do_update = 1 # download if update flag is set: if do_update == 1 then wget -O download/mx_ports.csv 'https://raw.githubusercontent.com/mex-fisheries/mex_ports/refs/heads/main/data/clean/mex_ports.csv' # remove spaces for all column headers in csv sed -i '1 s/ /_/g' download/mx_ports.csv # load into postGIS with: ogr2ogr -lco GEOMETRY_NAME=geom -f PostgreSQL PG:"" download/mx_ports.csv -oo X_POSSIBLE_NAMES=longitude -oo Y_POSSIBLE_NAMES=latitude -s_srs EPSG:4326 -t_srs EPSG:4326 -nlt point -nln mx_ports ######### # Step 2 - SQL script (pseudo code): #### # remove duplicate points and make geometries valid: UPDATE mx_ports SET geom=ST_MakeValid(ST_RemoveRepeatedPoints(geom)); # add local geometry column and reproject existing geometries into local EPSG: SELECT AddGeometryColumn ( 'mx_ports', 'geom_local', 4326, 'point', 2 ); UPDATE mx_ports SET geom_local=ST_MakeValid(ST_RemoveRepeatedPoints(ST_Transform(geom,4326))); CREATE INDEX mx_ports_geom_local_idx ON mx_ports USING GIST (geom_local); NOTIFY pgrst, 'reload schema'; # Move into corrrect directory and create derivative directory in data package on osgeo cd /data/mx_ports/ mkdir -p derived # Create geojson of table in derivate directory on osgeo ogr2ogr -f GeoJSON -overwrite derived/mx_ports.json PG:"" -select "municipality_id,municipality_name,port_id,port_name,landing_site_id,latitude,longitude,geom" mx_ports # Create downloadable tarfile of geojson in derivative directory on osgeo rm -f derived/mx_ports.geojson.tar.gz tar -czf derived/mx_ports.geojson.tar.gz meta_dcat_mx_ports.json -C derived mx_ports.json # Move into correct directory cd /data/mx_ports/