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&...
Taking a look at the source code of PostGIS I found out how it parses SRIDs. Here is the correct way to specify the SRID in GeoJSON.
The GeoJSON specification says that the coordinates of a polygon are an array of line strings. Therefore I had to wrap them with additional brackets.
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 ...
The first step to determining the correct projection of any layer, is to find the projection information, if any, that came with your layer. In the case of a Shapefile, like what you downloaded from Census.gov, that information is contained in a .prj file, short for Projection.
Here are the contents of the projection file from the census data:
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
There are a couple of problems with your JSON.
Firstly, the coordinates should be an array of arrays.
Secondly, looking at the coordinates, it looks like the values are Latlong in a Geographic coordinate system, most probably EPSG:4326. That then needs to be transformed to EPSG:3857.
Once you correct these two things, you can insert the row, using the ...
To get the coordinate system of your table use the following:
SELECT ST_SRID(the_geom) FROM your_table_name LIMIT 1;
For your second question, I am not sure if EPSG:4283 is by default defined in spatial_ref_sys table. In case it is not, you can use a INSERT command. This definition is from spatialreference.org:
INSERT into spatial_ref_sys (srid, ...
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 (...
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 )
I would note that if you have curl support in your GDAL build (a very common configuration), you can simply give the spatialreference.org URL for the SRS and it will fetch what it needs and dereference it appropriately.
-a_srs http://spatialreference.org/ref/epsg/4326/ -t_srs http://spatialreference.org/ref/epsg/26915
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["...
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 ...
Following on from the answer given by @atlefren, an SRID usually is made of two components, an "authority" and an identifier. The authority is just the name of the organization that catalogues the identifiers. The most common authority you'll see is EPSG, which sands for the "European Petroleum Survey Group", and they have a comprehensive database of ...
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
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 ...
The definition of SRID 4326 is in fact IN DEGREES. ALso as it says in the ST_DWithin page:
Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid),
Since you're ...
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:
At a guess, your point should be about 5.5km southeast of Bad Reichenhall right?
If so, the problem you have is you're interpreting your input coordinates incorrectly. The clue is in the source projection's name: LAEA which stands for Lambert Azimuthal Equal-Area, which is a projection that uses linear units (metres in this case) rather than angular units.
There is no native way to do this in SQL Server (isn't ST_Transform a PostGIS method?). Look into something like SQL Server Spatial Tools and this post from a few weeks back. Also, you won't find any projected coordinate systems in the sys.spatial_reference_systems table as that table is only GEOGCS and used for geography datatypes.
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 ...
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)')
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 ...
Example for reprojection from EPSG:2193 to EPSG:3857
-sql "SELECT [Id], [Shape].STAsText() Shape FROM [SourceDbName].[dbo].[SourceTableName]"^
A Spatial Reference System Identifier (SRID) is a unique value used to unambiguously identify projected, unprojected, and local spatial coordinate system definitions.
In other words, a system of indentifiers for spatial reference system, so that i can tell a GIS application (like postGIS) that my data is in WGS84 Geographic coordinates by ...
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)",...
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.