locked
Select only linestrings which create Square polygons, RRS feed

  • Question

  • Hi,

    If I have a collection of linestrings, is it possible to only combine those linestring which make a square or rectangle ?

    Thx !


    Wednesday, June 13, 2012 8:19 PM

Answers

  • I wouldn't say "there's no other way than to leave SQL server" - it's just that there's nothing built-in to SQL server that provides this functionality.

    SqlGeometry and SqlGeography are CLR-types, so you can write any algorithms you want in .NET and run them in SQL Server. And, seeing as spatialite is open-source, you can examine the source and then implement your own Polygonise method based on the same process (assuming the result above is what you're after).


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by Maggie Luo Sunday, June 24, 2012 2:54 PM
    Monday, June 18, 2012 4:51 PM
    Answerer

All replies

  • I'm assuming you're using the geometry datatype? If so, are your squares/rectangles defined only out of 4 corner points? (Or 5, if the start/end point is repeated?). Are the corner points of squares/rectangles always defined in the same (clockwise/anticlockwise) order?

    It would be helpful if you could give some examples (i.e. WKT) of those LineStrings that you want to include and those you don't.


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Thursday, June 14, 2012 10:54 AM
    Answerer
  • Sorry if my explanation was a bit short :)

    Yes, I am working with geometry datatype and I have a collection of linestrings each linestring is either perfect horizontal or perfect vertical.

    Now I would like to combine only the linestrings which form a rectangle. -->only 4 corner points

    The linestrings are defined :

    - horizontal : top to bottom

    -vertical : left to right

    Thx !


    Thursday, June 14, 2012 11:16 AM
  • "...each linestring is either perfect horizontal or perfect vertical..." - so the LineStrings themselves aren't squares/rectangles - they are (or could be) considered to be the edges of a square/rectangle? In your first post I thought you were trying to identify those LineStrings that, in themselves, are square or rectangular?

    If it's the second interpretation, how many squares/rectangles is each edge allowed to form? What about LineString edges that form new rectangles by crossing/touching at a point somewhere along the LineString that is not a vertex - do you want to include those?

    I really think some sample data and expected results would help to understand what you're trying to do.

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    Thursday, June 14, 2012 11:52 AM
    Answerer
  • Thank you for putting so much effort in my question ! 

    The sample linestrings given or either horizontal or vertical.

    These linestring could be edges of a square/rectangle (in thise case they all are). Each edge can form as much rectangles that they want, I just want to know where
    linestrings form rectangles with eachother, of course it would be wise to just select the ones that are nearest or intersect...

    I Will explain my purpose with an invoice example

    You have a layout of an invoice and you can see al the important words like 'date,invoicenumber,total amount' into rectangle shapes
    So all the rectangles on an invoice contain important words. I need to know the area where there are rectangles to pull out those important keywords.

    These sample linestring are pull out from an invoice actually...


    linestring(69 961,1209 973)
    linestring(69 1041,1209 1051)
    linestring(69 1173,2375 1183)
    linestring(151 1265,2375 1277)
    linestring(429 2537,2037 2547)
    linestring(429 2655,2037 2665)
    linestring(429 2811,2037 2821)
    linestring(69 971,77 1261)
    linestring(351 971,359 1179)
    linestring(675 971,685 1179)
    linestring(1203 971,1211 1267)
    linestring(1509 1181,1517 1273)
    linestring(2369 1181,2377 1273)
    linestring(1777 1189,1783 1267)
    linestring(429 2545,437 2819)
    linestring(767 2545,775 2817)
    linestring(943 2545,953 2817)
    linestring(1245 2545,1255 2817)
    linestring(1533 2545,1543 2819)
    linestring(2031 2545,2039 2817)
    Thursday, June 14, 2012 12:36 PM
  • Anyone an idea ? :)
    Monday, June 18, 2012 6:54 AM
  • The algorithm you are looking for is Polygonize. Unfortunately, I didn't find an implementation for Sql Server or SqlGeometry.

    I've tried your data in Spatialite and got the expected results. Here's how i did it:

    1) Create the table:

    CREATE TABLE LINES("GEOM" GEOMETRY)

    2) Insert data:

    INSERT INTO LINES 
    SELECT (GeomFromText('linestring(69 961,1209 973)',0)) AS 'GEOM'
    UNION SELECT (GeomFromText('linestring(69 1041,1209 1051)',0))
    UNION SELECT (GeomFromText('linestring(69 1173,2375 1183)',0))
    UNION SELECT (GeomFromText('linestring(151 1265,2375 1277)',0))
    UNION SELECT (GeomFromText('linestring(429 2537,2037 2547)',0))
    UNION SELECT (GeomFromText('linestring(429 2655,2037 2665)',0))
    UNION SELECT (GeomFromText('linestring(429 2811,2037 2821)',0))
    UNION SELECT (GeomFromText('linestring(69 971,77 1261)',0))
    UNION SELECT (GeomFromText('linestring(351 971,359 1179)',0))
    UNION SELECT (GeomFromText('linestring(675 971,685 1179)',0))
    UNION SELECT (GeomFromText('linestring(1203 971,1211 1267)',0))
    UNION SELECT (GeomFromText('linestring(1509 1181,1517 1273)',0))
    UNION SELECT (GeomFromText('linestring(2369 1181,2377 1273)',0))
    UNION SELECT (GeomFromText('linestring(1777 1189,1783 1267)',0))
    UNION SELECT (GeomFromText('linestring(429 2545,437 2819)',0))
    UNION SELECT (GeomFromText('linestring(767 2545,775 2817)',0))
    UNION SELECT (GeomFromText('linestring(943 2545,953 2817)',0))
    UNION SELECT (GeomFromText('linestring(1245 2545,1255 2817)',0))
    UNION SELECT (GeomFromText('linestring(1533 2545,1543 2819)',0))
    UNION SELECT (GeomFromText('linestring(2031 2545,2039 2817)',0))

    3) Union and polygonize:

    SELECT AsText(Polygonize(GUnion(GEOM))) FROM LINES

    And here is the result:

    MULTIPOLYGON(((678.622479 1046.347566, 684.839933 1175.670598, 1208.593023 1177.94186, 1205.161252 1050.966327, 678.622479 1046.347566)), ((684.839933 1175.670598, 678.622479 1046.347566, 353.78839 1043.498144, 358.817569 1174.256798, 684.839933 1175.670598)), ((353.78839 1043.498144, 70.931502 1041.016943, 74.57308 1173.024168, 358.817569 1174.256798, 353.78839 1043.498144)), ((1249.231652 2660.100943, 1254.968258 2816.136619, 1542.960862 2817.927617, 1537.266139 2661.892202, 1249.231652 2660.100943)), ((1254.968258 2816.136619, 1249.231652 2660.100943, 947.162588 2658.222404, 952.899194 2814.25808, 1254.968258 2816.136619)), ((952.899194 2814.25808, 947.162588 2658.222404, 770.297721 2657.122498, 774.886795 2813.151037, 952.899194 2814.25808)), ((770.297721 2657.122498, 432.212262 2655.019977, 436.767834 2811.048307, 774.886795 2813.151037, 770.297721 2657.122498)))


    • Edited by vIndEx Monday, June 18, 2012 7:07 AM
    Monday, June 18, 2012 7:06 AM
  • So there is no other way then leave SQL Server en go for Spatialite ?
    Monday, June 18, 2012 12:03 PM
  • I wouldn't say "there's no other way than to leave SQL server" - it's just that there's nothing built-in to SQL server that provides this functionality.

    SqlGeometry and SqlGeography are CLR-types, so you can write any algorithms you want in .NET and run them in SQL Server. And, seeing as spatialite is open-source, you can examine the source and then implement your own Polygonise method based on the same process (assuming the result above is what you're after).


    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Marked as answer by Maggie Luo Sunday, June 24, 2012 2:54 PM
    Monday, June 18, 2012 4:51 PM
    Answerer