run SQL: CREATE EXTENSION postgis;
Enabling PostGIS
PostGIS is an optional extension that must be enabled in each database you want to use it in before you can use it. Installing the software is just the first step. DO NOT INSTALL it in the database called
postgres.
Connect to your database with
psql or PgAdmin. Run the following SQL. You need only install the features you want:-- Enable PostGIS (includes raster) CREATE EXTENSION postgis; -- Enable Topology CREATE EXTENSION postgis_topology; -- Enable PostGIS Advanced 3D -- and other geoprocessing algorithms -- sfcgal not available with all distributions CREATE EXTENSION postgis_sfcgal; -- fuzzy matching needed for Tiger CREATE EXTENSION fuzzystrmatch; -- rule based standardizer CREATE EXTENSION address_standardizer; -- example rule data set CREATE EXTENSION address_standardizer_data_us; -- Enable US Tiger Geocoder CREATE EXTENSION postgis_tiger_geocoder;
=============================================================
import shape file by postgis import/export loader,
which is under program file, postgis folder,
when you import shape file to PostGIS, did you set SRID?
My example is srid=2230, the default is 0, you must manually type in. The postgis import manager did NOT recognize the esri xxx.prj file, which has projection info, if you open that file, you can NOT see srid=2230. There are 2 ways you can get srid= 2230.
1) go to http://prj2epsg.org/search , upload your xxx.prj file, it will search for it and show you result 2230.
2)Or you can use arcCatalog, right click shape file, choose properties, you can find WKID:2230
If you failed to manually set SRID, while import, no worry,
you can use sql to update SRID. By doing this, you set all geometry in this table srid=2230.
Note: no actual coordinate transformation occur, only update the metadata about SRID to 2230, default is 0.
For my case, I excute SQL:
SELECT UpdateGeometrySRID('city_parks','geom',2230);
Finally, your geometry table has SRID=2230 Now you can output geojson to SRID=4326 by doing this:
select st_asgeojson(st_transform(geom,4326)) from city_parks;
This is give you correct lat, long coordinate which you can put on any web map, google, bing, leaflet, openlayers...etc...
Previous solution also work, because st_setsrid will literally update each of your geometry srid to 2230.
select st_asgeojson(st_transform(st_setsrid(geom,2230),4326)) from city_parks;
Summary:
You should manually set SRID while you import shape file, (in future, I hope POSTGIS import tool should automatically read esri's xxx.prj file and automatically popup the 2230 for me).
You should manually set SRID while you import shape file, (in future, I hope POSTGIS import tool should automatically read esri's xxx.prj file and automatically popup the 2230 for me).
If you forget to set SRID while import, you can always update your table's SRID metadata by SQL :
SELECT UpdateGeometrySRID('city_parks','geom',2230);
When you table's SRID is set correctly, you will be able to export to geojson by transform to SRID=4326.
