337
edits
Changes
Jump to navigation
Jump to search
====9/19/2017 TUESDAY''9AM-10:40AM & 1PM-2:20PM''====
* Useful 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 geogrphy:
'''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]
* Continue reading from: http://workshops.boundlessgeo.com/postgis-intro/geography.html
====9/20/2017 WEDNESDAY''3:50PM-5:20PM''====
* Attended first intro to GIS course yesterday
* Updated above notes on GIS
* Continue reading from:
====9/21/2017 THURSDAY''9AM-12:30PM''====
* 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
*Specifities/ Outliers to consider:
New York (decompose)
Princeton area (keep Princeton unique)
Reston, Virginia (keep)
San Diego (include La Jolla)
Silicon Valley (all distinct)
* Continue reading from: https://postgis.net/docs/postgis_installation.html
====9/25/2017 MONDAY''4PM-6PM''====
* Talked to Ed about GIS, Census data, and going about determining the correctness of reported 'place.' Currently script makes a cross product of each reported place and an existing place, outputting a column of boolean value to indicate whether the reported place's coordinates fell within a place's geometric boundaries. One other way of going about this which we discussed is to first check if the reported place does fall within that place's boundaries. If it isn't, we'll go about the cross product method.
* To add documentation :
** http://mcnair.bakerinstitute.org/wiki/PostGIS_Installation
** http://mcnair.bakerinstitute.org/wiki/Urban_Start-up_Agglomeration
* Discussed the need to maintain venture capital database.
*Relevant File paths:
**E:\McNair\Projects\Agglomeration\TestGIS.sql
**Z:\VentureCapitalData\SDCVCData\vcdb2\ProecssingCoLevelSimple.sql
**Z:\VentureCapitalData\SDCVCData\vcdb2\CitiesWithGT10Active.txt
====9/26/2017 TUESDAY ''9AM-12PM''====
* Created a table that maps a state to the database name.
** http://mcnair.bakerinstitute.org/wiki/PostGIS_Installation#Translating_Table_names_to_corresponding_States
* Added more GIS-information (functions, realm & outliers to consider)
** http://mcnair.bakerinstitute.org/wiki/Urban_Start-up_Agglomeration#GIS_Resources
* Visualization in PostGIS or connecting to ArcGIS for visualization (import/export data)
* Spatial indexing:
** http://revenant.ca/www/postgis/workshop/indexing.html
.
.
.
====10/2/2017 MONDAY ''4PM-6PM''====
* Talked to Harrison & Peter regarding ArcGIS
** Currently have points plotted on Houston
** Trouble interpreting geometry type, as currently reads in from text file
** Documents located in : E:\McNair\Projects\Agglomeration\HarrisonPeterWorkArcGIS
* Attempted to install PostGIS spatial extention from PostgreSQL but getting 'spatial database creation failed' error message.
** Referenced instructions:
*** https://www.gpsfiledepot.com/tutorials/installing-and-setting-up-postgresql-with-postgis/
*** http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01
no edit summary
** New output to be found in: E:\McNair\Projects\OliverLovesCircles\out
2017-10-02:
* Talked to Harrison & Peter regarding ArcGIS
** Currently have points plotted on Houston
** Trouble interpreting geometry type, as currently reads in from text file
** Documents located in : E:\McNair\Projects\Agglomeration\HarrisonPeterWorkArcGIS
* Attempted to install PostGIS spatial extention from PostgreSQL but getting 'spatial database creation failed' error message.
** Referenced instructions:
*** https://www.gpsfiledepot.com/tutorials/installing-and-setting-up-postgresql-with-postgis/
*** http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01
2017-09-26:
* Created a table that maps a state to the database name.
** http://mcnair.bakerinstitute.org/wiki/PostGIS_Installation#Translating_Table_names_to_corresponding_States
* Added more GIS-information (functions, realm & outliers to consider)
** http://mcnair.bakerinstitute.org/wiki/Urban_Start-up_Agglomeration#GIS_Resources
* Visualization in PostGIS or connecting to ArcGIS for visualization (import/export data)
* Spatial indexing:
** http://revenant.ca/www/postgis/workshop/indexing.html
2017-09-25:
* Talked to Ed about GIS, Census data, and going about determining the correctness of reported 'place.' Currently script makes a cross product of each reported place and an existing place, outputting a column of boolean value to indicate whether the reported place's coordinates fell within a place's geometric boundaries. One other way of going about this which we discussed is to first check if the reported place does fall within that place's boundaries. If it isn't, we'll go about the cross product method.
* To add documentation :
** http://mcnair.bakerinstitute.org/wiki/PostGIS_Installation
** http://mcnair.bakerinstitute.org/wiki/Urban_Start-up_Agglomeration
* Discussed the need to maintain venture capital database.
*Relevant File paths:
**E:\McNair\Projects\Agglomeration\TestGIS.sql
**Z:\VentureCapitalData\SDCVCData\vcdb2\ProecssingCoLevelSimple.sql
**Z:\VentureCapitalData\SDCVCData\vcdb2\CitiesWithGT10Active.txt
2017-09-21:
* 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
*Specifities/ Outliers to consider:
New York (decompose)
Princeton area (keep Princeton unique)
Reston, Virginia (keep)
San Diego (include La Jolla)
Silicon Valley (all distinct)
* Continue reading from: https://postgis.net/docs/postgis_installation.html
2017-09-20:
* Attended first intro to GIS course yesterday
* Updated above notes on GIS
2017-09-19:
* Useful 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 geogrphy:
'''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]
* Continue reading from: http://workshops.boundlessgeo.com/postgis-intro/geography.html
</onlyinclude>
* Read documentation on PostGIS and tiger geocoder
* Continue reading from: http://workshops.boundlessgeo.com/postgis-intro/joins.html
