Re: How can I add a custom EPSG to the Map (Geometry) Viewer in PGAdmin4,

Dave,

EPSG is a structured data set of Coordinate Reference Systems and
Coordinate Transformations - the data set was originally designed by the
European Petroleum Survey Group (EPSG) but is from 2005 maintained by the
International Oil and Gas Association Survey Committee.

Each EPSG has a unique Spatial Reference Identifier (SRID), and as
indicated above it defines both the DATUM (Coordinate Frame, or in more
daily English it's a model of the Earth), the PROJECTION (or no
projection), and the necessary parameters for transforming data from one
EPSG to another.

The EPSG = 4326 which Avin references above is the most basic/common EPSG
of all: the datum is WGS-84 (defined by the US and used in their GPS
system), and it uses decimal degrees with no projection: north-south
LATITUDES go from 0 degrees at the equator to 90 degrees at the North Pole
and -90 degrees at the South Pole, and east-west LONGITUDES go from the 0
degree meridian (historically called the "Greenwich Meridian") to 180
degrees eastwards and -180 degrees westwards.

US datums and projections (like UTM, Universal Transverse Mercator) became
dominant for global cartography after World War 2 due to the US economic
and military dominance, which continued with their World Geodetic System
1984 datum (WGS-84) used for their GPS Global Navigation Satellite System
(GNSS) - but that dominance is slowly fading and there are more global
players in the GNSS arena:
- the Russian GLONASS GNSS uses the PZ-90 datum
- the European GALILEO GNSS uses the GTRF datum
- the Chinese BIDOU GNSS uses the CGCS2000 datum
Before these recent global datums we had several hundred local datums,
based on land surveying by either military or civilian surveys (guys
trekking from hilltop to hilltop using theodolites and the like).

The global datums above is always to some extent a compromise, trying to
average out errors for the whole globe. Which is why countries are still
creating variants of these because they want/need higher accuracy for
things like cadastral mapping (land parcel ownership boundaries), where you
usually require 5cm or better accuracy in urban areas to avoid too many
lawyer feasts when neighbours start feuding over the fence. South Africa,
for instance, have their own "Hartebeeshoek" datum that differs from WGS-84
by 20-30 centimetres only... (Geographers are funny creatures - the
adrenaline rush the rest of us get from e.g. kinky sex, they get from
improving geodetic accuracy by 7 millimetres...)

Now add a large number of potential projections - where the round earth
surface is projected onto a flat surface - and you see why POSTGIS have
over 5,000 EPSGs in its spatial reference system table.

For normal users of navigational data (smart-phones etc), it's usually
relatively simple: Modern GNSS chipsets are increasingly seamlessly
handling and combining the signals from all satellites (31 GPS, 24 GLONASS,
27 GALILEO, 27 BEIDOU), and they convert it into whatever EPSG/SRID the
user prefer or whatever is used by their apps.

This was probably more than you wanted to know about EPSG, but you can
always dump it again when your grey cell ram gets full ;-)

Best regards
Calle

On Thu, 1 Aug 2019 at 12:45, Avin Kavish <avinkavish(at)gmail(dot)com> wrote:

> Hey bobb,
>
> but we have a custom EPSG, where do I add this into PGAdmin4
>>
>
> EPSGs are stored in a table called spatial_ref_sys. All you need to do is
> insert a record that contains the definition in well known text format and
> proj4 format (for transformation). I'd recommend checking the table first
> because there over 5000 EPSGs in it. epsg.io shoud give you an idea of
> what the insert statement should look like. Example
>
> INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 4326, 'EPSG', 4326, '+proj=longlat +datum=WGS84 +no_defs ', 'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]')
>
>
> Also, it would be nice to add in our own background mapping layers as
>> well, is there a method for adding in WMS/WFS to the layer list?
>
>
> Till support for custom layers land, once you have added your custom EPSG
> to the database you can transform the geometries to SRID 4326 and assuming
> they are on Earth, they will show up on the map. The following functions
> will be helpful
>
> st_srid - Check the SRID of a shape
> st_setsrid - Change an SRID without transforming coordinates
> st_transform - Transforms a geometry from one EPSG to another
>
> So when you are selecting data from a different EPSG, use: select
> st_transform(geometry, 4326) from table_name and it will enable the world
> map. AFAIK no other EPSG is supported for viewing on the map without
> transformation. I discovered it by sheer accident.
>
>
>
> On Wed, Jul 31, 2019 at 7:54 PM Basques, Bob (CI-StPaul) <
> bob(dot)basques(at)ci(dot)stpaul(dot)mn(dot)us> wrote:
>
>> All,
>>
>> Following the appreciation thread and saw the reference to the geometry
>> viewer, I didn’t even know that was in there. Anyway, nice, but we have a
>> custom EPSG, where do I add this into PGAdmin4?
>>
>> Also, it would be nice to add in our own background mapping layers as
>> well, is there a method for adding in WMS/WFS to the layer list?
>>
>> Thanks
>>
>> bobb
>>
>>
>>
>>

--

*Carl-Anders (Calle) Hedberg*

HISP

Researcher & Technical Specialist

Health Information Systems Programme – South Africa

Cell: +47 41461011 (Norway)

Iridium SatPhone: +8816-315-19119 (usually OFF)

E-mail1: calle(at)hisp(dot)org

E-mail2: calle(dot)hedberg(at)gmail(dot)com

Skype: calle_hedberg