An SRID is a coordinate system. We're taught in (traditional / Arc) "GIS" to always store your data in a projected coordinate system, because we're going to eventually use some calculation like 'area', so we'd better store our data in a coordinate system that gives us that measurement.
However, PostGIS throws that concept out the window.
Here's a good Q&...
When you specify a geometry without an SRID, it is actually 0 (or -1 for version < 2):
SELECT ST_SRID('POINT(-122.334172173172 46.602634395263560)'::geometry);
So when you use this geometry with another one with SRID=4326, it is mixing 0 and 4326. This is usually a useful error, if the spatial references are truly different. ...
Well, technically, NAD83 is not a subset of WGS84. If you mine further in the SpatialReference.org projetion definitions, you can see the difference between the two projections.
PROJ.4 definiton of NAD83:
+proj=longlat +ellps=GRS80 +datum=NAD83 +no_defs
PROJ.4 definition of WGS84:
+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs
As you can see, the two ...
To add a column to an existing table, use the ALTER TABLE DDL, e.g.:
ALTER TABLE my_table
ADD COLUMN the_geom_mercator
which can be populated from another column (the_geom) using:
UPDATE my_table SET
the_geom_mercator = ST_Transform(the_geom, 3857)
WHERE ST_SRID(the_geom) = srid;
(the third line FROM ...
Assuming your areas of interest are rather small compared to the globe, you could set up a custom transverse mercator projection.
You have to know the geographic coordinates lat_0 and lon_0 of the origin of your CRS, and the direction of x and y axis:
+proj=tmerc +lat_0=51.4 +lon_0=7 +k=1 +x_0=0 +y_0=0 +ellps=WGS84 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs
What's happening is that the data you are trying to insert does not have an SRID assigned. To assign one, try wrapping the inserted geometry in your insert statement with ST_SetSRID(). E.g.,
INSERT INTO public.borne (num_borne, shape_borne)
(SELECT num_borne, ST_SetSRID(shape_borne, 26191)
INSERT INTO public.borne (...
It's easy here
Look it up on http://www.spatialreference.org
Find it SR-ORG:7069 NAD_1983_HARN_StatePlane_Michigan_South_FIPS_2113_IntlFeet
Click PostGIS spatial_ref_sys INSERT statement
Run that insert command.
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 97069, 'sr-org', 7069, '', 'PROJCS["...
There is no EPSG SRID 102743. Note that EPSG is the authority, and 102743 is the SRID. If you look up SRID 102743 on spatialreference.org, the listing is for ESRI:102743, meaning that ESRI (the publishers of ArcGIS) is the authority, not EPSG (European Petroleum Survey Group, now absorbed by the International Association of Oil & Gas Producers). The ...
The function checks for same in and out srid, and returns untouched if so, as the source code shows:
* If input SRID and output SRID are equal, return geometry
* without transform it
if ( input_srid == output_srid )
In principle, it should always be lat/lon as that is what the current EPSG database defines it as. Unfortunately, over the years computer scientists have visited and made a decision to use lon/lat as that works for their high school maths mapping to X,Y and is easy.
So whenever you receive a file of coordinates in EPSG:4326 you need to check who sent them to ...
They're mostly from the EPSG - https://www.epsg-registry.org/
To be unique, you have to specify where they are from (basically a namespace - EPSG:4326, not just 4326). However most people will interpret them as EPSG if not specified.
Originally, the EPSG registry was maintained in a Microsoft Access database and the well-known IDs (WKIDs) were assigned by ...
If you want/need to have more info from within PostGIS, running something like
left(split_part(srtext, '"', 1), -1) AS "type",
split_part(srtext, '"', 2) AS "name",
split_part((regexp_split_to_array(srtext, 'UNIT\[\"'))[array_length(regexp_split_to_array(srtext, 'UNIT\[\"'), 1)], '"', 1) AS units
You don't need constraints for PostGIS 2.x, just us typmods. For 2D geometries with SRID=4326, the typmod is geometry(Geometry,4326). Or for M-dimension geometries, it would be geometry(GeometryM,4326) (you get the idea).
create temp table sometable(wkt geometry(Geometry,4326));
insert into sometable(wkt) values('SRID=4326;POINT(1 2)')
If column in your table has explicitly defined geometry type and srid, like:
CREATE TABLE geom_table (id serial, geom geometry(point,3857);
Your database will not allow to place there any other geometries than point in srid 3857... but you can also define table like this:
CREATE TABLE geom_table (id serial, geom geometry);
Than you can store there ...
A good modern web resource is epsg.io
It's "modern" because it has been synchronized to recent versions of the EPSG reference database, as stated at the bottom of the web page. It's source is available on GitHub.
It's also pretty easy to use. For example, SRID=4326, here is the description web page and links to various formats:
ST_SetSRID will set the coordinate reference system of your geometry. This will allow PostGIS commands to understand how your grid will relate to other geometries.
Using ST_SetSRID is not essential, as long as all other geometries you may query against are known to be on the same grid. However, if you query against another table that has a SRID set, even if ...
UpdateGeometrySRID only ever sets the SRID identifier (and changes the typemod accoridngly), it does not reproject!
This scenario is covered by the docs:
If you got the projection wrong (or brought it in as unknown) in load and you wanted to transform to web mercator all in one shot you can do this with DDL but there is no equivalent PostGIS management ...
Just copy the string into a text file named crs.txt, and run gdalsrsinfo crs.txt >> out.txt in the OSGeo4W Shell or Linux Terminal on it:
PROJ.4 : '+proj=longlat +ellps=intl +no_defs '
OGC WKT :
DATUM["Not specified (based on International 1924 ellipsoid) (EPSG ID",
SPHEROID["International 1924 (EPSG ID 7022)",...
Because spatialreference.org pre-dates PostGIS 2.0, when the SRID key limitation was added
No problems with your workaround
sr.org is basically defunct, there's no active maintainers, you can file a ticket for posterity and the next maintainers at http://trac.osgeo.org/metacrs/ under the sr.org category. The '9' was probably tacked on to avoid conflicting ...
Example for reprojection from EPSG:2193 to EPSG:3857
-sql "SELECT [Id], [Shape].STAsText() Shape FROM [SourceDbName].[dbo].[SourceTableName]"^
There could be two possibilities. If the coordinates are in km and is the top of a ridgeline/mountain (approximately lat:46.853795 lon:7.985557), the CRS could be CH1903 / LV03, EPSG::21781.
If the coordinates are in meters and using CH1903 / LV03C-G (EPSG::21782), they're near Greyerzstrasse in Berne as Joseph suggested. However. EPSG states that 21782 is ...
With ST_SetSRID it is possible to define explicitly what the coordinates like (-122.079513,45.607703) mean: for example that they are WGS84 long-lat degrees https://epsg.io/4326 and not something else in some other system like https://epsg.io/3857.
As documented for example in https://postgis.net/docs/ST_GeogFromText.html EPSG:4326 is the default for ...
After trying the above with no luck, I tried the method below:
This gives the SRID value for all records in the table. I would assume that since all records SHOULD have the same SRID, that if you wanted to simply get the value for one record it would tell you the SRID for the entire table:
If you are typing a geometry's WKT into pgAdmin III, you need to specify the SRID using EWKT syntax, otherwise it defaults to 0.
Type this into the geometry field of pgAdmin III:
Otherwise, @MakinFlippyFloppy has the correct SQL way, using ST_SetSRID.
Usually, the unit is given for each CRS inside its documentation, so you can find it directly in the CRS information, for example :
if you don't find the information on your system (, you can check on spatialreference.org
Be aware that sometimes ...
You want to use ST_DWithin() which takes advantage of indexing
WHERE ST_DWithin(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography, 200)
ORDER BY ST_Distance(latlong::geography, ST_SetSRID(ST_Point(16.520, 47.846), 4326)::geography);
See also http://postgis.net/2013/08/26/tip_ST_DWithin/
Yes, this is a rounding error, a slight difference in the result depending on order of operands.
Not sure what ST_LengthSpheroid() differs, but it's possible there's a slightly different code path still, as they originally had completely different code paths.
You're talking about a difference of 0.00000000000002, there's no "most accurate" answer. (A ...
I've found a couple of useful tools to find possible CRSes if you know the general area.
epsg.io : this is a web site which is a search engine for CRSes. Type in something like 'Switzerland' and it'll list possible matching CRSes. You can also see the CRS extent on a map and see metadata in various formats (proj4, WKT etc) so you can check that units and ...
epsg:32717 is a UTM projection in meters, not degrees, which covers a thin strip from Ecuador down through Peru.
You're telling it the point coordinates you have are in epsg:32717. But these seem to be longitude and latitude, consistent with Ecuador.
That parameter doesn't project data for you. You're just tagging the coordinates with that CRS. To ...
This is one of the most infamous issues in the GIS world: setting vs. transforming a CRS.
Take your time and get familiar with coordinate references, projections and transformations - it will make your life easier.
To the issue at hand; two hands-off options are
simply load both layers in their native CRS into QGIS, with the project CRS set to your desired ...