Re: [postgis-users] Coordinate format for SRID:4326
Michael,
On a computer, use decimal degrees. x axis first (abscissa - Eastings or
Longitude), y axis second (ordinate - Northings or Latitude).
In the cockpit, you may find yourself using sexagesimal degrees, and you may
hear people quote 'Lat-lon', but if you look at the def's below, you will see
+proj=longlat
just my opinion...
cheers
Ben
On 11/07/2011, at 10:40 PM, Gheorghiu, Mihai wrote:
> I am confused by the references to "sexagesimal degrees" that I found on this
> list and elsewhere on the Web.
> SRID:4326 used for geography representation in postgis is based on EPSG:4326
> (http://spatialreference.org/ref/epsg/4326/postgis/):
> INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
> values ( 94326, 'epsg', 4326, '+proj=longlat +ellps=WGS84 +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.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]');
> If I query Oracle 10g
> SELECT * FROM MDSYS.SDO_CS_SRS WHERE SRID='4326';
> WGS 844326 4326 EPSG. See 3D CRS for original
> information source. GEOGCS [ "WGS 84", DATUM ["World Geodetic
> System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137,
> 298.257223563]], PRIMEM [ "Greenwich", 0.00 ], UNIT ["Decimal Degree",
> 0.01745329251994328]]
> My understanding of "decimal degrees" is that 72 degrees 30 minutes are
> represented as 72.5, and that's the way I used to input coordinates in Oracle.
> Now if I want to input the same value in postgis, do I have to use a format
> such as 72d30m00s00 ("sexagesimal degrees"), or 72.5 ("decimal degrees")?
> To add to the confusion, in postgis there is SRID:900914, which is the exact
> replica of Oracle's 8307, and both use the term "decimal degrees".
>
> Thank you very much for your support.
>
> Michael
>
> The information contained in this transmission is to be considered
> CONFIDENTIAL and PROPRIETARY to Consortium Health Plans, Inc. and intended
> for the use of the Individual or Entity named above. If the reader of this
> message is not the Intended Recipient, you are hereby notified that any
> dissemination, distribution, or copying of this communication is Strictly
> Prohibited. If you have received this transmission in error, please notify us
> immediately by telephone at 410-772-2900 or return email to sender
> immediately. Thank You. ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Newbie - Spatial Join with a large dataset
Rich, If you've got those details then you should be good to go. Use the ST_Within Function to select the points that are within the polygons of the contients that you want to show. http://www.postgis.org/docs/ST_Within.html Cheers James On 11 July 2011 18:54, Rich B wrote: > Thank you! > Yes I do have both of those datasets. I'll run the spatial join that way and > see what happens. > *fingers crossed* > > > > On Jul 11, 2011, at 5:20 AM, James David Smith > wrote: > >> Hey Rich, >> >> I'm a newbie myself, but it seems to me that you either need a table >> that defines the geometry of the continents (so that you can do a >> spatial join with the points), or the points table itself needs a >> column which tells you in which continent that point is located. Do >> you have either of these at the moment? >> >> Cheers >> >> James >> >> On 10 July 2011 20:34, Rich B wrote: >>> Hello all, >>> >>> I have a newbie question for you guys…here goes : >>> >>> >>> >>> I have a large dataset (a geonames world data-dump) that I’ve imported into >>> Postgis. >>> >>> I want to know how to do a Spatial Join with this dataset so that I can >>> split the millions of points up into chunks by continent. >>> >>> For example : >>> >>> A user connects to the database in QGIS and wants to select all of the >>> cities (I’m going to trim the data down a little more using a query later >>> on) and they want to bring in only the points that fall in Europe or Africa. >>> >>> In a nutshell I want a user to select all the points by continent instead of >>> loading the whole dataset. >>> >>> >>> >>> I’m sure there’s an easy way to do this , and I’m probably going to slap >>> myself for not thinking of this earlier. >>> >>> I’m relatively new to OSGIS and I’m getting the hang of it. I’ve been an >>> ESRI guy for a while. >>> >>> >>> >>> Thanks in advance for the feedback. >>> >>> >>> >>> Cheers!!! >>> >>> ___ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >>> >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Newbie - Spatial Join with a large dataset
Thank you! Yes I do have both of those datasets. I'll run the spatial join that way and see what happens. *fingers crossed* On Jul 11, 2011, at 5:20 AM, James David Smith wrote: > Hey Rich, > > I'm a newbie myself, but it seems to me that you either need a table > that defines the geometry of the continents (so that you can do a > spatial join with the points), or the points table itself needs a > column which tells you in which continent that point is located. Do > you have either of these at the moment? > > Cheers > > James > > On 10 July 2011 20:34, Rich B wrote: >> Hello all, >> >> I have a newbie question for you guys…here goes : >> >> >> >> I have a large dataset (a geonames world data-dump) that I’ve imported into >> Postgis. >> >> I want to know how to do a Spatial Join with this dataset so that I can >> split the millions of points up into chunks by continent. >> >> For example : >> >> A user connects to the database in QGIS and wants to select all of the >> cities (I’m going to trim the data down a little more using a query later >> on) and they want to bring in only the points that fall in Europe or Africa. >> >> In a nutshell I want a user to select all the points by continent instead of >> loading the whole dataset. >> >> >> >> I’m sure there’s an easy way to do this , and I’m probably going to slap >> myself for not thinking of this earlier. >> >> I’m relatively new to OSGIS and I’m getting the hang of it. I’ve been an >> ESRI guy for a while. >> >> >> >> Thanks in advance for the feedback. >> >> >> >> Cheers!!! >> >> ___ >> postgis-users mailing list >> postgis-users@postgis.refractions.net >> http://postgis.refractions.net/mailman/listinfo/postgis-users >> >> > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Coordinate format for SRID:4326
I am confused by the references to "sexagesimal degrees" that I found on this
list and elsewhere on the Web.
SRID:4326 used for geography representation in postgis is based on EPSG:4326
(http://spatialreference.org/ref/epsg/4326/postgis/):
INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 94326, 'epsg', 4326, '+proj=longlat +ellps=WGS84 +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.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]');
If I query Oracle 10g
SELECT * FROM MDSYS.SDO_CS_SRS WHERE SRID='4326';
WGS 844326 4326 EPSG. See 3D CRS for original
information source. GEOGCS [ "WGS 84", DATUM ["World Geodetic
System 1984 (EPSG ID 6326)", SPHEROID ["WGS 84 (EPSG ID 7030)", 6378137,
298.257223563]], PRIMEM [ "Greenwich", 0.00 ], UNIT ["Decimal Degree",
0.01745329251994328]]
My understanding of "decimal degrees" is that 72 degrees 30 minutes are
represented as 72.5, and that's the way I used to input coordinates in Oracle.
Now if I want to input the same value in postgis, do I have to use a format
such as 72d30m00s00 ("sexagesimal degrees"), or 72.5 ("decimal degrees")?
To add to the confusion, in postgis there is SRID:900914, which is the exact
replica of Oracle's 8307, and both use the term "decimal degrees".
Thank you very much for your support.
Michael
The information contained in this transmission is to be considered CONFIDENTIAL
and PROPRIETARY to Consortium Health Plans, Inc. and intended for the use of
the Individual or Entity named above. If the reader of this message is not the
Intended Recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is Strictly Prohibited. If you
have received this transmission in error, please notify us immediately by
telephone at 410-772-2900 or return email to sender immediately. Thank You.___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Raster for Met Data
> - Original Message - > > For sure doing overlay gis operations like st_intersection is not > > possible when the srid of the two geometries is different. In this > > case you would have to reproject (ST_Transform) the geometry so it fit > > the srid of right grid. But if your application is able to decide > > which grid it must query then it should also be able to decide which > > tale it must query and hence putting all your grids in different table > > should not be a problem. > > For the general case (particularly in the coast example, where the queries are > basically just Lon/Lat), we would not know which grid contains the answer. > Consequently we would have to do two queries (one to determine the grid to > query and one to query the specific grid), where today we make do with one. I > suspect that this would cost us a fair bit of performance. You could have a table of the outlines of each dataset and use CASE to determine to which srid reproject your point. All that in one query. > > You can easily get the x,y upperleft corner of a pixel with the > > Raster2WorldcoordX(0,0) and Raster2WorldcoordY(1,0) functions. Is that > > what you need? > > Not quite - I was thinking more of World2RasterCoord, but using a geometry to > extract multiple raster points. Essentially, ST_INTERSECT, but returning > raster > coordinates rather than world coordinates and value. You can extract every point of a complex geometry using a mixture of ST_PointN(),ST_NPoints(),ST_NumGeometries() and ST_GeometryN(). Have a look in the PostGIS doc. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Trigger function with ST_Intersects
Hello list, I have a point table, and a polygon table. The polygon layer contains a column which I want to use to populate a similar column in the point layer, using ST_Intersects (point in polygon overlay). I want to write this query into a trigger function, which will run the above query every time a new point is added. This should be very trivial query but I don't seem to get the syntax right. Any help very much appreciated! Pekka Hurskainen ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Newbie - Spatial Join with a large dataset
Hey Rich, I'm a newbie myself, but it seems to me that you either need a table that defines the geometry of the continents (so that you can do a spatial join with the points), or the points table itself needs a column which tells you in which continent that point is located. Do you have either of these at the moment? Cheers James On 10 July 2011 20:34, Rich B wrote: > Hello all, > > I have a newbie question for you guys…here goes : > > > > I have a large dataset (a geonames world data-dump) that I’ve imported into > Postgis. > > I want to know how to do a Spatial Join with this dataset so that I can > split the millions of points up into chunks by continent. > > For example : > > A user connects to the database in QGIS and wants to select all of the > cities (I’m going to trim the data down a little more using a query later > on) and they want to bring in only the points that fall in Europe or Africa. > > In a nutshell I want a user to select all the points by continent instead of > loading the whole dataset. > > > > I’m sure there’s an easy way to do this , and I’m probably going to slap > myself for not thinking of this earlier. > > I’m relatively new to OSGIS and I’m getting the hang of it. I’ve been an > ESRI guy for a while. > > > > Thanks in advance for the feedback. > > > > Cheers!!! > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Raster for Met Data
Hi, Mixed grid queries are frequent, at least on something like yr. Assuming it does work with a single table, I guess the only way to really find out whether it works well is to try it. :-) Regards, Michael A. - Original Message - > On Thu, Jul 07, 2011 at 11:06:34PM +, Michael Akinde wrote: > > > Due to the number of grids I am somewhat leery of setting up (and > > tearing down) multiple tables on an operational database (data grids > > change over time). Keeping it in one table would presumably be > > better, assuming it is practicable. > > It is practicable to keep everything in one table, but if you never > get items from mixed grids in the same spatial query you may give > the optimizer better informations by keeping those grids in separate > tables. For instance, querying for all items in a given bounding box > might return X items from the lowest-resolution grid, X*2 from the > next higher resolution, X*4 for the next and so on the estimator > would think it's so many rows that an index scan is not worth the cost > while indeed you may be just querying the lowest-resolution one. > > But it's just a guess, only you know the kind of queries you're going > to run and how the planner deals with them. > > --strk; > > () Free GIS & Flash consultant/developer > /\ http://strk.keybit.net/services.html > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Raster for Met Data
Hi, Thanks for the responses. - Original Message - > For sure doing overlay gis operations like st_intersection is not > possible when the srid of the two geometries is different. In this > case you would have to reproject (ST_Transform) the geometry so it fit > the srid of right grid. But if your application is able to decide > which grid it must query then it should also be able to decide which > tale it must query and hence putting all your grids in different table > should not be a problem. For the general case (particularly in the coast example, where the queries are basically just Lon/Lat), we would not know which grid contains the answer. Consequently we would have to do two queries (one to determine the grid to query and one to query the specific grid), where today we make do with one. I suspect that this would cost us a fair bit of performance. > > No, the former is handled quite nicely by ST_INTERSECT, if I've > > understood > > correctly. I was thinking of a function that returns the x, y > > offsets; i.e., 0 0 for > > the origin point, 1 0 for the next point along the x axis, and so > > on. Since we have > > our value data stored in files outside the database, the x,y offsets > > allow us to > > retrieve data points very rapidly. This is how we have it > > implemented in WDB > > today, but with our own very limited function for identifying those > > offsets. > > You can easily get the x,y upperleft corner of a pixel with the > Raster2WorldcoordX(0,0) and Raster2WorldcoordY(1,0) functions. Is that > what you need? Not quite - I was thinking more of World2RasterCoord, but using a geometry to extract multiple raster points. Essentially, ST_INTERSECT, but returning raster coordinates rather than world coordinates and value. > But normally, if you store your rasters in the database, you don't > have to do that anymore to get the raster value associated with a > point. You can just do: > > SELECT ST_Value(rast, ST_MakePoint(x,y)) > FROM yourgridtable > WHERE ST_Intersects(rast, ST_MakePoint(x,y)) I understand that. I'm dubious as to whether we can store out rasters in the database though, given the limitations to single SRID. Thus my trying to figure out alternatives approaches for us to work with the raster support in PostGIS. Regards, Michael A. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
