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

Thanks for the detailed info!

On Thu, Aug 1, 2019 at 10:21 PM Calle Hedberg <calle(dot)hedberg(at)gmail(dot)com>
wrote:

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

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company