Go to content Go to navigation Go to search

Featured Articles:

    What sorts of Services do I offer?
    What people say about me or my work...
    Book: "Applying And Extending Oracle Spatial"
    Analyzing Spatial Query Performance Improvements in Oracle Spatial and Graph 12c Through Cross-Vendor Comparison

Newest Article:

PostGIS: Creating a line inside a polygon

Friday November 16 2018 at 22:01

I love working with the PostGIS API.

It is a deep and wide river to swim in.

But sometime having read, researched, experimented, and not found the right solution one can be forced to roll one’s own so to speak to get the job done. If someone can point out a way to create what is needed using standard PostGIS API calls, then let me know.

The problem is this. The customer wants to create transects from specific points inside a polygon, at a specific angle. What is not known is the distance the line needs to be to have its starting point touch one boundary, and its ending point to touch another.

The function is called ST_InsideLine as has a header than looks like this:

  1. CREATE OR REPLACE FUNCTION spdba.ST_InsideLine(
  2.     p_point           IN geometry,
  3.     p_direction_start IN NUMERIC,
  4.     p_direction_end   IN NUMERIC,
  5.     p_polygon         IN geometry,
  6.     p_dIncrement      IN NUMERIC DEFAULT 5.0
  7. )
  8. RETURNS GEOMETRY
  9. ...

The function’s parameters are:

The function works as follows:

This function creates a line that lies inside p_polygon but whose start and end points touch p_polygon’s boundary.

Line is generated from a starting point and two bearings.
The algorithm first generates a line from the supplied point to the line’s start point by extending and testing the line by p_dIncrement until it finds a p_polygon boundary.
After finding point for first half of line, the second line is generated using p_direction_end.
If p_direction_end is null or the same as p_direction_start, a default direction of p_direction_start – 180.0 is used.
The algorithm uses a stepping approach: it first creates a line at p_direction_start for p_dIncrement distance. If the line does not touch a p_polygon boundary point it increases the line length by p_dIncrement and tests again.
The stepping process continues until the line touches or crosses the boundary.
Once the two halves are created, they are unioned together and the resulting line returned.

Here is an example:

  1. SELECT ST_AsText(
  2.          ST_SnapToGrid(
  3.            spdba.ST_InsideLine(
  4.              ST_SetSrid(ST_Point(82,60),28355),
  5.              0.0::NUMERIC,
  6.              generate_series(10,350,10)::NUMERIC,
  7.              ST_GeomFromText('POLYGON((8.003 66.926, 11.164 70.086, 13.692 70.929, 19.171 70.929, 23.385 70.508, 26.546 70.297, 33.078 71.983, 36.871 74.301, 43.824 75.776, 51.199 75.986, 59.206 74.511, 62.788 71.772, 64.685 70.719, 73.535 71.351, 78.592 69.244, 83.649 64.187, 84.913 62.501, 86.178 57.022, 85.756 53.019, 85.124 49.226, 86.81 45.433, 87.863 40.376, 89.338 37.215, 89.338 32.58, 87.653 27.522, 83.438 18.462, 81.12 15.933, 74.799 17.619, 77.538 25.205, 80.067 30.472, 80.488 37.215, 78.381 41.219, 75.22 53.229, 72.06 60.394, 62.999 63.133, 52.463 65.451, 46.353 66.926, 37.714 63.344, 29.496 62.501, 20.646 61.447, 14.114 62.922, 9.899 61.447, 3.157 63.765, 3.367 64.187, 8.003 66.926))',28355),
  8.              5.0
  9.        ),
  10.            0.001
  11.        )
  12.        ) AS geom ;
  13. -- results
  14. --
  15. LINESTRING(82.875 64.961,82 60,82 65.836)
  16. LINESTRING(83.557 64.279,82 60,82 65.836)
  17. LINESTRING(84.083 63.608,82 60,82 65.836)
  18. LINESTRING(84.529 63.014,82 60,82 65.836)
  19. LINESTRING(84.924 62.453,82 60,82 65.836)
  20. LINESTRING(85.08 61.778,82 60,82 65.836)
  21. LINESTRING(85.22 61.172,82 60,82 65.836)
  22. LINESTRING(85.354 60.591,82 60,82 65.836)
  23. LINESTRING(85.49 60,82 60,82 65.836)
  24. LINESTRING(85.639 59.358,82 60,82 65.836)
  25. LINESTRING(85.811 58.613,82 60,82 65.836)
  26. LINESTRING(86.027 57.675,82 60,82 65.836)
  27. LINESTRING(86.127 56.537,82 60,82 65.836)
  28. LINESTRING(85.991 55.244,82 60,82 65.836)
  29. LINESTRING(85.798 53.421,82 60,82 65.836)
  30. LINESTRING(85.374 50.729,82 60,82 65.836)
  31. LINESTRING(87.701 27.667,82 60,82 65.836)
  32. LINESTRING(82 16.893,82 60,82 65.836)
  33. LINESTRING(78.611 40.781,82 60,82 65.836)
  34. LINESTRING(77.031 46.348,82 60,82 65.836)
  35. LINESTRING(76.119 49.814,82 60,82 65.836)
  36. LINESTRING(75.482 52.232,82 60,82 65.836)
  37. LINESTRING(74.872 54.019,82 60,82 65.836)
  38. LINESTRING(74.216 55.506,82 60,82 65.836)
  39. LINESTRING(73.585 56.937,82 60,82 65.836)
  40. LINESTRING(72.938 58.402,82 60,82 65.836)
  41. LINESTRING(72.234 60,82 60,82 65.836)
  42. LINESTRING(58.022 64.228,82 60,82 65.836)
  43. LINESTRING(40.559 75.083,82 60,82 65.836)
  44. LINESTRING(55.77 75.144,82 60,82 65.836)
  45. LINESTRING(68.869 71.018,82 60,82 65.836)
  46. LINESTRING(72.535 71.28,82 60,82 65.836)
  47. LINESTRING(76.052 70.302,82 60,82 65.836)
  48. LINESTRING(78.66 69.176,82 60,82 65.836)
  49. LINESTRING(80.751 67.085,82 60,82 65.836)

Visually, the result looks like this:

Line Inside Polygon

The code is available as part of my package of PostGIS functions on my shop

My Services

I offer the following services to customers:

  1. Integration of GeoSpatial within IT
    Advice and guidance on how to fully integrate geospatial data and processing needs within existing, pure, IT environments (using existing GIS software or without purchasing proprietary GIS software);
  2. Independent Solutions Advice and Sanity Checking
    Solutions not Software ;
    How to efficiently and effectively solve a business requirement via existing technology;
    Does what you have been offered in a tender really stack up? Or do you want independent quality assurance of a tender document before submitting it to a customer?
    When it is best to use Open Source as against Commercial off the shelf (COTS) and vice versa?
    Is buzzword-compliant (SOA, BPEL, J2EE, .NET, WebServices…) technology really needed to solve a business problem? If so, what is the best way to achieve this?
    ;
  3. Analyzing ROI of your current GIS software technology
    Matching business problems to existing software investment. Are you getting value from your software investment? How to improve that ROI;
  4. Systems Review
    Does the solution you have been provided actually match your requirements?;
  5. Database Benchmarking:
    How well are you using your Oracle Spatial/SQL Server Spatial/PostGIS database?
    How good is your databases documentation/metadata?
    Is the integrity and quality of the data stored in your databases as good as you think?
    Is your use of Oracle (Spatial), SQL Server Spatial consistent with industry best-practice?
    Get advice on some simple “rules” to follow to improve your database!
  6. Database Consulting through design, test, build and configuration services
    Ensuring your databases reflect your needs via:
    1. Data Quality Rules Documentation, Conformance Testing, Correction and Certification;
    2. Data Conversion, Migration, Loading;
    3. Database Design;
    4. Coding of appropriate in-Database processing:
      Via design and coding services in:
      1. PL/SQL – Oracle,
      2. PL/pgSQL – PostgreSQL,
      3. T-SQLSQL Server,
      4. Java,
      5. C#.
    5. Database Performance Tuning;
    6. Spatially enabling Data Warehouses and Processes;
    7. ArcSDE Configuration and Tuning.
  7. SpatialDB Training Courses and Staff Mentoring
    Spatial SQL Training on your datasets;
    After Training support
    ;
  8. Free Software
  9. Commercial Support
    GeoRaptor
    Free PL/SQL packages
    (bug fixes, extensions and new functionality).

I have over 25 years experience with database technologies from mainframes through to personal computers. I have 25 years Oracle use and am recognised by the Oracle Spatial development team as one of the early adopters of Oracle’s Sdo_Geometry data type. In 2011 that experience was recognised when Oracle awarded me the “2011 Oracle Spatial Excellence Award for Education and Research”.

I was on the original beta program for SQL Server 2008 Spatial (aka “Katmai”). While most of my solutions work centres on Oracle, I now have quite a few years experience designing, programming, tuning and working with SQL Server. I make available over 200 TSQL functions to SQL Server Spatial users across multiple versions.

I also have the necessary PostgreSQL/PostGIS and MySQL experience to help you implement the best spatial database solution possible.

I have (next to the original designer and programmer) the longest experience of using ArcSDE in the industry and can help you get the best out of this piece of ESRI software and show you how to make it work with other spatial software (esp open source). Also, with the new ESRI Spatial Type for Oracle, PostgreSQL and SQL Server, I am in a unique position to help you plan your migrations from SDEBINARY to either the ESRI Spatial Type, SQL Server spatial or Oracle’s Sdo_Geometry.

My experience is not limited just to databases. I have managed “GIS” infrastructure management teams, designed and built systems integrating spatial processing within business systems in the areas of mapping, editing, analysis, GPS field data capture etc.

Finally, as a geospatial professional with IT and Oracle (Certified Professional SQL 10gR2; Certified Associate DBA 10gR2) qualifications and experience, I can help guide the uptake of geospatial technologies into business’s IT infrastructures.

Let me help you cost-effectively spatially-enable your enterprise.