Day 25. 如何在地圖上畫Pokemon #2：儲存繪圖API建立

第 12 屆 iThome 鐵人賽

DAY 25

Modern Web

WebGIS入門學習 - 以Openlayers實作系列第 25 篇

12th鐵人賽 webgis openlayers draw

JYHsu

2020-10-05 00:35:54

984 瀏覽

分享至

接續昨天的繪圖，今天要將繪製的圖形存到使用者的個人繪圖當中。

今日的大綱

儲存頁面建立
資料庫建立
儲存繪圖API建立
前端圖形feature拆解
前端介接儲存繪圖API

1. 儲存頁面建立

接續昨天的內容，把繪圖儲存功能補上，於Draw.html內加入儲存頁面。

<div class="ui bottom attached tab segment" data-tab="save">
    <table style="width:100%" class="attributetable" id="saveattribute">
        <tr>
            <td>標題：</td>
            <td>
                <div class="ui fluid input">
                    <input type="text" id="savetitle">
                </div>
            </td>
        </tr>
        <tr>
            <td>內容：</td>
            <td>
                <div class="ui fluid input">
                    <input type="text" id="savecontent">
                </div>
            </td>
        </tr>
        <tr>
            <td colspan="2" style="text-align:center;">
                <button type="button" class="ui fluid blue button" id="savedraw" onclick="draw.savedraw()">儲存</button>
            </td>
        </tr>
    </table>
</div>

預設標題為今天的日期，為了方便將 Date的prototye 進行擴充，方便日期格式的轉換。

Date.prototype.Format = function (fmt) { //author: meizz 
    var o = {
        "M+": this.getMonth() + 1, //月份 
        "d+": this.getDate(), //日 
        "h+": this.getHours(), //小时 
        "m+": this.getMinutes(), //分 
        "s+": this.getSeconds(), //秒 
        "q+": Math.floor((this.getMonth() + 3) / 3), //季度 
        "S": this.getMilliseconds() //毫秒 
    };
    if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt;
}

預設標題為今天的日期，並將日期格式轉換為 yyyy-MM-dd 格式。

$('#savetitle').val(new Date().Format("yyyy-MM-dd"));

儲存繪圖頁面示意圖：

2. 資料庫建立

建立 dbo.UserDrawSave 資料表，用於儲存使用者的繪圖，分別針對以下欄位進行解釋：

OBJECTID：流水號。
drawsaveid：繪圖feature識別碼。
userid：使用者ID。
geomstr：feature的geometry轉存成字串。
stylestr：feature的樣式轉存成字串。
DDate：日期。
title：儲存繪圖的title。
info：儲存繪圖的描述。

USE [OLDemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserDrawSave](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL,
	[drawsaveid] [nvarchar](30) NOT NULL,
	[userid] [nvarchar](30) NOT NULL,
	[geomstr] [nvarchar](max) NULL,
	[stylestr] [nvarchar](max) NULL,
	[DDate] [datetime] NULL,
	[title] [nvarchar](30) NULL,
	[info] [nvarchar](256) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

3. 儲存繪圖API建立

STEP1. 首先先建立Model

DrawGraphicList：每一個feature都拆分為geometry和style分別儲存。
UserDrawInput：繪圖儲存的輸入。
UserDrawOutput：繪圖相關API的輸出欄位。

public class DrawGraphicList
{
    public string geom { get; set; }
    public string style { get; set; }
}

public class UserDrawInput
{
    public string SQLtype { get; set; }
    public string title { get; set; }
    public string info { get; set; }
    public string drawsaveid { get; set; }
    public List<DrawGraphicList> features { get; set; }
}

public class UserDrawOutput : DrawGraphicList
{
    public string userid { get; set; }
    public string drawsaveid { get; set; }
    public string title { get; set; }
    public string info { get; set; }
}

STEP2. 新增SQL預存程序 [dbo].[procUserDrawSQL]，用於進行使用者繪圖相關的SQL查詢，包含新增、刪除、查詢。

USE [OLDemo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[procUserDrawSQL]
	-- Add the parameters for the stored procedure here
	@SQLtype nvarchar(20),
	@userid nvarchar(30),
	@drawsaveid nvarchar(30),
	@title nvarchar(30),
	@info nvarchar(256),
	@geomstr nvarchar(MAX),
	@stylestr nvarchar(MAX)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
IF @SQLtype = 'Add'
		BEGIN
			INSERT INTO  [OLDemo].[dbo].[UserDrawSave]	([drawsaveid], [userid], [geomstr], [stylestr], [DDate], [title], [info])
			VALUES (@drawsaveid, @userid, @geomstr, @stylestr, GETDATE(), @title, @info);
			SELECT [drawsaveid], [userid], [geomstr], [stylestr], [DDate], [title], [info] FROM [OLDemo].[dbo].[UserDrawSave] where [drawsaveid]=@drawsaveid and [userid]=@userid order by [DDate], [OBJECTID];
		END
	ELSE
		BEGIN 
			IF @SQLtype = 'Del'
				BEGIN
					DELETE FROM [OLDemo].[dbo].[UserDrawSave] WHERE [drawsaveid]=@drawsaveid and  [userid]=@userid ;
				END
			ELSE
				BEGIN
			SELECT [drawsaveid], [userid], [geomstr], [stylestr], [DDate], [title], [info] FROM [OLDemo].[dbo].[UserDrawSave] where [userid]=@userid order by [DDate], [OBJECTID];
				END ;
		END ;
END
GO

STEP3. 建立相關的Infrastructure function

於BasicInfoFunc.cs新增 UserDrawFeaturesSQL()，利用SQLtype傳入DB，執行 [dbo].[procUserDrawSQL] 進行相對應的動作。

public static async Task<List<UserDrawOutput>> UserDrawFeaturesSQL(DrawGraphicList UserDraw, string userid, string SQLtype, string title, string info, string drawsaveid)
{
    SqlDataReader reader = null;
    SqlConnection myConnection = new SqlConnection();
    string Constr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
    myConnection.ConnectionString = Constr;
    SqlCommand sqlCmd = new SqlCommand();
    string sqlStr;
    sqlStr = "exec [dbo].[procUserDrawSQL] @SQLtype=@p1,@userid=@p2,@drawsaveid=@p3,@title=@p4,@info=@p5,@geomstr=@p6,@stylestr=@p7 ";
    sqlCmd.Parameters.AddWithValue("@p1", SQLtype.Trim());
    sqlCmd.Parameters.AddWithValue("@p2", userid.Trim());
    sqlCmd.Parameters.AddWithValue("@p3", drawsaveid.Trim());
    sqlCmd.Parameters.AddWithValue("@p4", title.Trim());
    sqlCmd.Parameters.AddWithValue("@p5", info == null ? "" : info.Trim());
    sqlCmd.Parameters.AddWithValue("@p6", UserDraw == null? "" : (UserDraw.geom == null ? "" : UserDraw.geom.Trim()));
    sqlCmd.Parameters.AddWithValue("@p7", UserDraw == null ? "" : (UserDraw.style == null ? "" : UserDraw.style.Trim()));
    sqlCmd.CommandText = sqlStr;
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.Connection = myConnection;
    List<UserDrawOutput> data = new List<UserDrawOutput> { };
    try
    {
        myConnection.Open();
        reader = sqlCmd.ExecuteReader();
        while (reader.Read())
        {
            data.Add(new UserDrawOutput()
            {
                userid = reader["userid"].ToString(),
                drawsaveid = reader["drawsaveid"].ToString(),
                title = reader["title"].ToString(),
                info = reader["info"].ToString(),
                geom = reader["geomstr"].ToString(),
                style = reader["stylestr"].ToString()
            });
        }
        myConnection.Close();
        myConnection.Dispose();
        return data;
    }
    catch (Exception ex)
    {
        throw;
    }
}

STEP4. 新增Controller與對應的API

新增 BasicController.cs，用於基本資料的查詢API控制器。

using OLMapAPI.Models;
using Swashbuckle.Swagger.Annotations;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using OLMapAPI.Infrastructure.auth;
using OLMapAPI.Infrastructure.BasicInfo;

namespace OLMapAPI.Controllers
{
    [Authorize]
    [RoutePrefix("api/Basic")]
    public class BasicController : ApiController
    {
    // BasicController API 功能
    }
}

建立使用者圖形操作功能 UserDrawFeatures_SQL() 。
圖形繪製識別碼 drawsaveid 採用「G - 日期（yyyyMMddHHmmss） - 檢查碼」的格式進行建立；檢查碼為0~9的隨機亂數取值，藉由這種編碼方式應能確保不會有重複的ID出現。

此外，將features拆解，利用迴圈一一地將每個feature寫入資料表當中。

/// <summary>
/// 使用者圖形操作功能
/// </summary>
/// <returns></returns>
[Route("UserDrawFeatures_SQL")]
[HttpPost]
[SwaggerResponse(HttpStatusCode.OK, "OK", typeof(List<UserDrawOutput>))]
public async Task<HttpResponseMessage> UserDrawFeatures_SQL(UserDrawInput UserDraw)
{
    bool isValidJson = authFunc.IsValidJson(UserDraw);
    string token = authFunc.parseTokenFromHeader(this.Request);
    string userid = authFunc.getUserNameByToken_true(token);
    if (!isValidJson)
    {
        string message = "非法JSON格式";
        HttpResponseMessage response = Request.CreateErrorResponse(HttpStatusCode.BadRequest, message);
        return response;
    }
    if (string.IsNullOrEmpty(UserDraw.SQLtype))
    {
        return Request.CreateResponse(HttpStatusCode.BadRequest, "Bad Request");
    }
    else
    {
        UserDraw = authFunc.translateNullIntoEmpty(UserDraw);
        try
        {
            DateTime myDate = DateTime.Now;
            Random rnd = new Random();
            string drawsaveid = UserDraw.drawsaveid == "" ? "G-" + myDate.ToString("yyyyMMddHHmmss") + "-" + rnd.Next(0, 10).ToString() : UserDraw.drawsaveid;
            if (UserDraw.features.Count != 0)
            {
                for (int i = 0; i < UserDraw.features.Count - 1; i++)
                {
                    await BasicInfoFunc.UserDrawFeaturesSQL(UserDraw.features[i], userid, UserDraw.SQLtype, UserDraw.title, UserDraw.info, drawsaveid);
                }
                return Request.CreateResponse(HttpStatusCode.OK, await BasicInfoFunc.UserDrawFeaturesSQL(UserDraw.features[UserDraw.features.Count - 1], userid, UserDraw.SQLtype, UserDraw.title, UserDraw.info, drawsaveid));
            }
            else
            {
                return Request.CreateResponse(HttpStatusCode.OK, await BasicInfoFunc.UserDrawFeaturesSQL(null, userid, UserDraw.SQLtype, UserDraw.title, UserDraw.info, drawsaveid));
            }
        }
        catch (Exception SqlException)
        {
            return Request.CreateResponse(HttpStatusCode.InternalServerError, "Internal Server Error");
        }
    }
}

4. 前端圖形feature拆解

在前端，繪製完圖以後，是一個Openlayers的自有格式的Object，如下圖。

可以發現主要構成這個圖形的有 圖案的geometry 和 style樣式，因此撰寫 exportfeature() 將這兩個obj拆出來並轉為string，後續將存到資料庫內。

function exportfeature(f) {
    var geomstring = JSON.stringify(new ol.format.GeoJSON().writeGeometryObject(f.getGeometry()));
    var stylestring = JSON.stringify(f.getStyle());
    return {
        geom: geomstring,
        style: stylestring
    };
}

5. 前端介接儲存繪圖API

由上述html可看出，按下儲存繪圖按鈕，即執行 savedraw() ，將drawLyr裡面的所有feature跑迴圈，利用exportfeature()將geom和style分解出來，併入即將作為API的input Data的json檔當中。

介接 /Basic/UserDrawFeatures_SQL API 將所有圖面上的feature存入資料庫當中，最後執行getUserDrawCaseList() 得到使用者繪圖列表 (為Day 26 的內容)。

function savedraw() {
    var drawsaveobj = {
        "SQLtype": "Add",
        "title": $("#savetitle").val(),
        "info": $("#savecontent").val(),
        "features": []
    };
    var featureall = map.e_getLayer("drawLyr").getSource().getFeatures();
    if (featureall.length !== 0 && confirm("是否要儲存圖面繪圖？")) {
        featureall.forEach(function (item, idx) {
            var stringObj = draw.exportfeature(item);
            drawsaveobj.features.push(stringObj);
            if (idx === featureall.length - 1) {
                $.ajax({
                    type: "POST",
                    url: config_OLMapWebAPI + "/Basic/UserDrawFeatures_SQL",
                    headers: {
                        "Authorization": localStorage["token"]
                    },
                    data: JSON.stringify(drawsaveobj),
                    dataType: "json",
                    contentType: "application/json; charset=utf-8",
                    success: function (d) {
                        //var data = $.parseJSON(d.d);
                        var data = d;
                        console.log(data);
                        alert("繪圖儲存成功！");
                        draw.getUserDrawCaseList();
                    },
                    error: function (jqXHR, exception) {
                        ajaxError(jqXHR, exception);
                    }
                });
            }
        });
        console.log(drawsaveobj);
    } else {
        alert("無繪圖圖形！");
    }
},

資料庫儲存繪圖資訊示意圖：