postgis import shape file output geojson

Create new database, must create postgis extension, otherwise would not import shape file.

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).

      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.