1

I have to convert a .shp into a .csv file and upload it to the SQL Server, for a program that only supports Polygon format and can not render Points.

Is there a way to convert Points to Polygon or is it easier if I just make a SQL script and convert Points to Polygon manually?

POINT (13.5766500015713 45.5163335041142)

This should technically be a square, right?

POLYGON ((13.5766600015713 45.5163235041142, 
                13.5766600015713 45.5163435041142,
                13.5766400015713 45.5163435041142,
                13.5766400015713 45.5163235041142))
  • 1
    Are you sure the program only accepts multipolygons? If simple polygons were okay, you could buffer your points by 1 mm, thus creating polygons and be done with it. – Erik Sep 1 at 11:28
  • Yeah, I am the author of the program. It can only display on type of shape/layer, because otherwise I would have to introduce a second layer to display Points and it would complicate things drastically. – Adephx Sep 1 at 11:31
  • 1
    Is there a specific reason for using explicitly multi-polygons? – Erik Sep 1 at 12:09
  • The library that I am using can only load shapes by type. Each type has to be bound to a different layer. – Adephx Sep 1 at 12:17
  • 1
    No, the resulting objects would not be squares, but spheroidal trapezoids (but only if you first added the required closing vertex -- five points make a quadrilateral). You're also using eight too many digits of precision -- 11 nanometers is overkill. – Vince Sep 1 at 12:29
0

This is a COMPLETE hack, but it worked for me:

  1. I opened the .csv file in Excel (be sure to open it Data > From Text, as Text. It can also done in SQL directly, but it turned out to be more hassle than it's worth) and used a combination of "Find and Replace" and "Text to Column" to un-merge the original WKT column:

    from: POINT (13.5766500015713 45.5163335041142) into: into 13.5766500015713 | 45.5163335041142
    
  2. I then created a new SQL table "CENTROIDS", imported the .csv file and ran this query:

    SELECT WKT1+0.00001 AS WKT1_1, WKT2-0.00001 AS WKT1_2, WKT1+0.00001 AS WKT2_1, WKT2+0.00001 AS WKT2_2, WKT1-0.00001 AS WKT3_1, WKT2+0.00001 AS WKT3_2, WKT1-0.00001 AS WKT4_1, WKT2-0.00001 AS WKT4_2, [SID]
        INTO CENTROID_NEW
        FROM CENTROID
    
  3. I then exported the table back to a .cvs file and merged everything back to a single column with the following Excel forumla:

    ="POLYGON (("&A2&" "&B2&","&C2&" "&D2&","&E2&" "&F2&","&G2&" "&H2&","&A2&" "&B2&"))"
    
  4. I then imported the file back to the SQL server.

Again, this is a COMPLETE hack, only meant as a last resource.

| improve this answer | |
  • You need five coordinate pairs for valid WKT. The last point must be the same as the first. – Vince Sep 1 at 14:24
  • Care to elaborate? I was sure that three points should be enough? My program loads data just fine even with just three points. – Adephx Sep 1 at 14:32
  • You need four for a triangle. In WKT the ring of a polygon must include the identical first and last coordinate, otherwise it is not "closed". I assume Vince meant five because of your mention of a square in the original question. – bugmenot123 Sep 1 at 15:13
  • I double checked. All of the data does indeed include duplicated coordinates, even though it feels a bit redundant, because it could just loop to the first coordinate. That being said, my data loads just fine even without the duplicated coordinate, so go figure? I'll correct it anyway. – Adephx Sep 1 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.