Working with PostgreSQL - Revision history

Ed at 15:40, 21 October 2019

2019-10-21T15:40:54Z

Ed: /* SQL Commands */

2019-08-27T20:34:37Z

SQL Commands

Ed: /* Connecting to the dbase server */

2019-03-17T16:44:25Z

Connecting to the dbase server

Ed at 16:37, 7 March 2019

2019-03-07T16:37:05Z

Ed: /* Perl Functions */

2017-11-19T22:45:03Z

Perl Functions

Ed: /* EXPLAIN */

2017-11-17T03:24:56Z

EXPLAIN

Ed: /* Dumping and Restoring a Database */

2017-08-01T00:06:53Z

Dumping and Restoring a Database

Ed: /* Python Functions */

2017-05-02T16:49:08Z

Python Functions

Ed: /* SQL Commands */

2017-03-20T21:06:17Z

SQL Commands

Ed at 18:43, 20 March 2017

2017-03-20T18:43:41Z

← Older revision		Revision as of 15:40, 21 October 2019
Line 231:		Line 231:
	$$ LANGUAGE plpythonu;		$$ LANGUAGE plpythonu;

		+	==PostGIS Resources==
		+
		+	See:
		+	*http://postgis.net/features/
		+	*https://www.census.gov/geo/maps-data/data/tiger-line.html
		+	*https://www.census.gov/geo/maps-data/data/tiger.html
		+	*https://en.wikipedia.org/wiki/GIS_file_formats
		+
		+	===Useful PostGIS functions for spatial joins===
		+
		+	'''sum(expression)''': aggregate to return a sum for a set of records
		+	'''count(expression)''': aggregate to return the size of a set of records
		+	'''ST_Area(geometry)''' returns the area of the polygons
		+	'''ST_AsText(geometry)''' returns WKT text
		+	'''ST_Buffer(geometry, distance)''': For geometry: Returns a geometry that represents all points whose distance from this Geometry is less than or equal to distance. Calculations are in the Spatial Reference System of this Geometry. For geography: Uses a planar transform wrapper.
		+	'''ST_Contains(geometry A, geometry B)''' returns the true if geometry A contains geometry B
		+	'''ST_Distance(geometry A, geometry B)''' returns the minimum distance between geometry A and geometry B
		+	'''ST_DWithin(geometry A, geometry B, radius)''' returns the true if geometry A is radius distance or less from geometry B
		+	'''ST_GeomFromText(text)''' returns geometry
		+	'''ST_Intersection(geometry A, geometry B)''': Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84
		+	'''ST_Intersects(geometry A, geometry B)''' returns the true if geometry A intersects geometry B
		+	'''ST_Length(linestring)''' returns the length of the linestring
		+	'''ST_Touches(geometry A, geometry B)''' returns the true if the boundary of geometry A touches geometry B
		+	'''ST_Within(geometry A, geometry B)''' returns the true if geometry A is within geometry B
		+	geometry_a '''&&''' geometry_b: Returns TRUE if A’s bounding box overlaps B’s.
		+	geometry_a '''=''' geometry_b: Returns TRUE if A’s bounding box is the same as B’s.
		+	'''ST_SetSRID(geometry, srid)''': Sets the SRID on a geometry to a particular integer value.
		+	'''ST_SRID(geometry)''': Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table.
		+	'''ST_Transform(geometry, srid)''': Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.
		+	'''ST_Union()''': Returns a geometry that represents the point set union of the Geometries.
		+	'''substring(string [from int] [for int])''': PostgreSQL string function to extract substring matching SQL regular expression.
		+	'''ST_Relate(geometry A, geometry B)''': Returns a text string representing the DE9IM relationship between the geometries.
		+	'''ST_GeoHash(geometry A)''': Returns a text string representing the GeoHash of the bounds of the object.
		+
		+	===Native functions for geography===
		+
		+	'''ST_AsText(geography)''' returns text
		+	'''ST_GeographyFromText(text)''' returns geography
		+	'''ST_AsBinary(geography)''' returns bytea
		+	'''ST_GeogFromWKB(bytea)''' returns geography
		+	'''ST_AsSVG(geography)''' returns text
		+	'''ST_AsGML(geography)''' returns text
		+	'''ST_AsKML(geography)''' returns text
		+	'''ST_AsGeoJson(geography)''' returns text
		+	'''ST_Distance(geography, geography)''' returns double
		+	'''ST_DWithin(geography, geography, float8)''' returns boolean
		+	'''ST_Area(geography)''' returns double
		+	'''ST_Length(geography)''' returns double
		+	'''ST_Covers(geography, geography)''' returns boolean
		+	'''ST_CoveredBy(geography, geography)''' returns boolean
		+	'''ST_Intersects(geography, geography)''' returns boolean
		+	'''ST_Buffer(geography, float8)''' returns geography [1]
		+	'''ST_Intersection(geography, geography)''' returns geography [1]
		+
		+	===Functions for Linear Referencing===
		+	'''ST_LineInterpolatePoint(geometry A, double measure)''': Returns a point interpolated along a line.
		+	'''ST_LineLocatePoint(geometry A, geometry B)''': Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point.
		+	'''ST_Line_Substring(geometry A, double from, double to)''': Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length.
		+	'''ST_Locate_Along_Measure(geometry A, double measure)''': Return a derived geometry collection value with elements that match the specified measure.
		+	'''ST_Locate_Between_Measures(geometry A, double from, double to)''': Return a derived geometry collection value with elements that match the specified range of measures inclusively.
		+	'''ST_AddMeasure(geometry A, double from, double to)''': Return a derived geometry with measure elements linearly interpolated between the start and end points. If the geometry has no measure dimension, one is added.
		+
		+	===3-D Functions===
		+	'''ST_3DClosestPoint''' — Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line.
		+	'''ST_3DDistance''' — For geometry type Returns the 3-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.
		+	'''ST_3DDWithin''' — For 3d (z) geometry type Returns true if two geometries 3d distance is within number of units.
		+	'''ST_3DDFullyWithin''' — Returns true if all of the 3D geometries are within the specified distance of one another.
		+	'''ST_3DIntersects''' — Returns TRUE if the Geometries “spatially intersect” in 3d - only for points and linestrings
		+	'''ST_3DLongestLine''' — Returns the 3-dimensional longest line between two geometries
		+	'''ST_3DMaxDistance''' — For geometry type Returns the 3-dimensional cartesian maximum distance (based on spatial ref) between two geometries in projected units.
		+	'''ST_3DShortestLine''' — Returns the 3-dimensional shortest line between two geometries
		+
		+	===Relevant PostgreSQL Commands===
		+	'''\dt .''' Show all tables
		+	'''\q''' Exit table
		+
		+	===To make a circle===
		+
		+	SELECT ST_Buffer(''[desired point]'', ''[desired radius]'', 'quad_segs=8')
		+	FROM ''[desired table]''
		+	quad_segs=8 indicates circle
		+
		+	[[File: CirclePostGIS.png]]
		+
		+	For more precision in circle:
		+	SELECT ST_Transform(geometry(
		+	ST_Buffer(geography(
		+	ST_Transform( ''[desired point]'', 4326 )),
		+	''[desired radius]')),
		+	900913) FROM ''[desired table]''
		+	4326 and 900913 represent particular precision.
		+
		+	===Decimal Degrees===
		+
		+	We are working with longitude and latitude in decimal degrees. See https://en.wikipedia.org/wiki/Decimal_degrees
		+
		+	When converting radius to km, multiply by 111.3199. For area, multiple by (111.3199)^2=12,392.12013601.
	==Configuring a copy of Postgres on Windows==		==Configuring a copy of Postgres on Windows==

← Older revision		Revision as of 20:34, 27 August 2019
Line 182:		Line 182:

	See https://www.postgresql.org/docs/9.5/static/sql-createindex.html for more options		See https://www.postgresql.org/docs/9.5/static/sql-createindex.html for more options
		+
		+	===SEQUENCES===
		+
		+	If you want to create a sequence:
		+	CREATE SEQUENCE serial START 101;
		+
		+	To use the sequence call:
		+	nextval('serial');

	==Perl Functions==		==Perl Functions==

@@ Line 8: / Line 8: @@
   E:/installs
-Connect via SSH directly to:
+The preferred way to connect is from the RDP where you can stay inside the private network, and have gigabit connection speeds. Connect to:
   reseacher@199.188.177.215
    or
   researcher@ssh.edegan.com
 All of the data files (tab-delimited text) that need to be loaded in and out of a dbase for your project should be stored in:
-  Z:/bulk/YourDbase
+  /bulk/YourDbase
 Note: To make your life easy, map the database's bulk drive on your RDP account.  [[Help:Access_RDP_Sever#Mapping the Database Server as Z|Follow the instructions]] to do this. We refer to the database server's bulk drive as either dbase/bulk  or as Z:, as this is the drive letter most commonly mapped to.

@@ Line 1: / Line 1: @@
-[[Category: McNair Admin]]
+==PostgreSQL on edegan.com==
+We have a dedicated Postgres server available for use by interns, affiliates, and researchers. It is available by SSH directly and through the RDP.
 ===Connecting to the dbase server===
 To connect through the RDP get a copy of PuTTY (put PuTTY.exe on your desktop) from:
-  E:/McNair/Installs
+  E:/installs
-Connect via SSH to:
+Connect via SSH directly to:
-  reseacher@128.42.44.181
+  reseacher@199.188.177.215
    or
-  researcher@dbase.mcnaircenter.org
+  researcher@ssh.edegan.com
 All of the data files (tab-delimited text) that need to be loaded in and out of a dbase for your project should be stored in:
-  Z:/Bulk/YourDbase
+  Z:/bulk/YourDbase
-Note: To make your life easy, map the database's bulk drive on your RDP account.  [[Help:Access_RDP_Sever#Connecting_the_Database_Server|Follow the instructions]] to do this. We refer to the database server's bulk drive as either 181/bulk (where 181 is the last segment of its address) or as Z:, as this is the drive letter most commonly mapped to.
+Note: To make your life easy, map the database's bulk drive on your RDP account.  [[Help:Access_RDP_Sever#Mapping the Database Server as Z|Follow the instructions]] to do this. We refer to the database server's bulk drive as either dbase/bulk  or as Z:, as this is the drive letter most commonly mapped to.
 ==Working with psql==
@@ Line 31: / Line 30: @@
 Note: to use a local copy of psql (if you have it installed locally), connect using the username researcher and DBname:
-  psql -h 128.42.44.181 -U researcher dbname
+  psql -h dbase.edegan.com -U researcher dbname
 You MUST store all of your SQL commands in a file named yourfilename.sql that is stored in:
-  E:/McNair/Projects/YourProject/
+  E:/projects/YourProject/
 There are NO EXCEPTIONS to this. All of your code must go into a .sql file. Even exploratory code. You can copy out of there line by line to run code.
@@ Line 161: / Line 160: @@
 ===ALTER===
-DON'T DO THIS. CREATE A NEW TABLE INSTEAD!
+'''DON'T DO THIS. CREATE A NEW TABLE INSTEAD!'''
 Change a table with ALTER:
@@ Line 211: / Line 210: @@
 To get into database via terminal:
-) ssh researcher@128.42.44.181
+) ssh researcher@ssh.edegan.com
 ) cd \folder_name
 ) psql database_name
@@ Line 256: / Line 255: @@
 ===Basic Performance Tuning===
 You will almost surely want to 'performance tune' your postgresql database, as the default settings are near useless. In particular edit postgresql.conf (which is in the data directory of your install) to change:
@@ Line 274: / Line 275: @@
 Create a user using pgAdmin or the createuser command:
-  createuser ed_egan
+  createuser username
 And then create a database again using pgAdmin or the createdb command:
-  createdb -O ed_egan DBName
+  createdb -O username DBName
 [[admin_classification::IT Build| ]]

@@ Line 185: / Line 185: @@
 ==Perl Functions==
 PLPerl was installed into Template1 (and hence all new databases) when the server was first set up.
@@ Line 201: / Line 203: @@
   $$ LANGUAGE plperl;
   </nowiki>
 ==Python Functions==

← Older revision		Revision as of 03:24, 17 November 2017
Line 173:		Line 173:

	EXPLAIN ANALYZE SELECT * FROM x;		EXPLAIN ANALYZE SELECT * FROM x;
		+
		+	===CREATE OR DROP INDEX===
		+
		+	If the dbase is large or you just need things to run faster, add an index to your key fields.
		+
		+	CREATE UNIQUE INDEX title_idx ON films (title);
		+	CREATE INDEX title_idx ON films (title);
		+	DROP INDEX title_idx;
		+
		+	See https://www.postgresql.org/docs/9.5/static/sql-createindex.html for more options

	==Perl Functions==		==Perl Functions==

@@ Line 60: / Line 60: @@
 To reload this database:
   pg_restore -d DBName db.backup
 ==SQL Commands==

← Older revision		Revision as of 16:49, 2 May 2017
Line 165:		Line 165:
	EXPLAIN ANALYZE SELECT * FROM x;		EXPLAIN ANALYZE SELECT * FROM x;

		+	==Perl Functions==
		+
		+	PLPerl was installed into Template1 (and hence all new databases) when the server was first set up.
		+
		+	An example perl function is:
		+	<nowiki>
		+	CREATE OR REPLACE FUNCTION getint (text) RETURNS int AS $$
		+	if ($_[0]) {
		+	my $var=$_[0];
		+	if ($var=~/^\d\d\d\d\d\d\d+$/) {
		+	return 1;
		+	}
		+	return undef;
		+	}
		+	return undef;
		+	$$ LANGUAGE plperl;
		+	</nowiki>
	==Python Functions==		==Python Functions==

@@ Line 114: / Line 114: @@
   Load using:
-  \COPY tablename FROM 'filename.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
+  <nowiki>\COPY tablename FROM 'filename.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV</nowiki>
   Unload (copy to txt file) using:
-  \COPY tablename TO 'filename.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV
+  <nowiki>\COPY tablename TO 'filename.txt' WITH DELIMITER AS E'\t' HEADER NULL AS '' CSV</nowiki>
 ===SELECT===

@@ Line 165: / Line 165: @@
   EXPLAIN ANALYZE SELECT * FROM x;
-===Python Functions ===
+==Python Functions==
 [[Installing python in a database]] - if Python is not already installed