Here are example commands for installing the PostGIS extension into a gc
schema:
CREATE SCHEMA gc;
CREATE EXTENSION postgis SCHEMA gc;
Here are example commands to move PostGIS and its objects to a new schema for version 2.4.4 of PostGIS in case you've already installed it:
CREATE SCHEMA gc;
UPDATE pg_extension
SET extrelocatable = TRUE
WHERE extname = 'postgis';
ALTER EXTENSION postgis
SET SCHEMA gc;
ALTER EXTENSION postgis
UPDATE TO "2.4.4next";
ALTER EXTENSION postgis
UPDATE TO "2.4.4";
This isn't strictly necessary; just helpful:
ALTER DATABASE whatever
SET search_path = public,gc;
Your question is rather broad, but from what you say, the best format would be GeoJSON. OpenLayers, Leaflet and Google Maps all read and display GeoJSON, with a trivial amount of coding required. Here is the Open Layers 3 GeoJSON example. OpenLayers 2 and 3 also have (Geo)JSON writers, which allow you to serialize OpenLayers vector objects back to JSON for database storage.
As far as getting JSON from the server in the first place is concerned, you have a couple of options from GeoServer.
WFS. This allows reading and updating of data and will transmit data in a number of formats, including GeoJSON. See the GeoServer WFS docs for more information. Here is the OpenLayers 3 WFS example
WPS. This allows you to send remote queries to GeoServer and get the results back in your web client, the default being JSON, see the Geoserver docs. Here is an OpenLayers 2 WPS example. This approach actually allows you to do GIS/spatial operations on your geometries, buffer, union, etc, remotely and get the results back in the client.
There are other options too, such as rolling your own, which you will have to do if you are just using AJAX to make the request -- you will have to then code up the server side somehow. To get multiple features our of Postgres, along with their attributes, you need to create a FeatureCollection
. This can be done using the JSON datatype and the row_to_json and array_to_json functions. A typical query, which comes from Regina Obe, author of Postgis in Action, on the Boston Gis website looks like this:
SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As Features
FROM (SELECT 'Feature' As type,
ST_AsGeoJSON(lg.geog)::json As geometry,
row_to_json((SELECT l FROM (SELECT loc_id, loc_name) As l)
) As properties
FROM locations As lg )
As f )
As fc;
which is quite hard to look at when you first see it, but very powerful. This means you still have your geometries in a geometry field, so can do all the GIS analysis you want with, but still easily produce GeoJSON for display purposes.
There are also options using GeoDjango
and the vectorformats library which will also produce GeoJSON with attributes directly from a Postgres table. You didn't specifically mention Python/Django, but as Python is so popular among GIS people, I thought this worth mentioning. Here is a longer blog article about the GeoDjango to GeoJSON approach.
Finally, it is not strictly true that you can not do analysis on JSON once it is in Postgres. Since Postgres 9.2 there has been a JSON datatype and in the upcoming Postgres 9.4 there will be a binary JSON format, known as JSONB which will allow indexing and search directly on JSON. I am not necessarily recommending this, given the rich array of functions available on separate geometry columns, but I mention if for completeness. I believe this is in some ways a response to the growing popularity of key/value stores in NoSQL databases, such as MongoDB, and will in no way displace Postgis's geometry column for GIS type queries.
There is also the WKT format, that can be used in a similar fashion, but it is much more of a pain to add attributes to it, and there is GML/XML, which allows for the encoding of complex structures, but is a pain to work with in Javascript (many would say, in general), whereas JSON is native, so perfect for "WebGIS".
Sorry, for such a long answer, but your question is rather broad :D
Best Answer
Alternatively to (partially) porting data to PostgreSQL/PostGIS, you can now use Foreign data wrappers (FDW).
From https://wiki.postgresql.org/wiki/Foreign_data_wrappers
With FDW, from PostgreSQL, you can read tables from MySQL, create the necessary geometries using PostGIS functions and run the available formulas. then, you can push back the result to MySQL.