瀏覽分類:

C#

postgresql 快速產生 c# 類別

https://github.com/fabiortsf/postgreTableToCSharp/blob/master/generate-script-postgre-to-csharp.sql

 

DO $$
DECLARE cnt integer;
DECLARE v_schemaname varchar;
DECLARE v_tablename varchar;

--
DECLARE v_tabela varchar;
DECLARE v_cursor_colunas record;
DECLARE v_nome_coluna varchar;
DECLARE v_classe VARCHAR;
DECLARE v_tipo VARCHAR;
DECLARE v_schema_name VARCHAR;
BEGIN
	cnt := 1;
	--修改這邊
	v_schema_name := 'yourschema';

	 WHILE cnt <= (select count(*) from pg_tables where schemaname = v_schema_name) LOOP
		
		v_tabela :=
		(SELECT tablename FROM (
		select ROW_NUMBER() OVER(ORDER BY tablename) ROWID , tablename
		from pg_tables
		where schemaname = v_schema_name ) mytables
		where ROWID = cnt);


		--- gen class here
		  select table_name INTO v_tabela from information_schema.tables where table_schema = v_schema_name
		  and table_type = 'BASE TABLE'
		  and table_name = v_tabela; 

		   v_classe := E'\r\n' || 'public class ' || v_tabela || ' {' ||  E'\r\n';
		   FOR v_cursor_colunas IN
			SELECT column_name as coluna, is_nullable as isnull, data_type as tipo, character_maximum_length as tamanho
			FROM information_schema.columns
			WHERE table_schema = v_schema_name
			AND table_name   = v_tabela
		   LOOP
		      --TIPOS
		      IF v_cursor_colunas.tipo='character varying' THEN
			v_tipo:= 'string';
		      ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho=1 THEN
			v_tipo:= 'char';
		      ELSIF v_cursor_colunas.tipo='character' and v_cursor_colunas.tamanho<>1 THEN
			v_tipo:= 'string';
		      ELSIF v_cursor_colunas.tipo='timestamp with time zone' THEN
			v_tipo:= 'DateTime';
		      ELSIF v_cursor_colunas.tipo='boolean' THEN
			v_tipo:= 'bool';
		      ELSIF v_cursor_colunas.tipo='integer' THEN
			v_tipo:= 'int';
		      ELSIF v_cursor_colunas.tipo='numeric' THEN
			v_tipo:= 'double';
		      ELSIF v_cursor_colunas.tipo='text' THEN
			v_tipo:= 'string';
		      ELSE
			v_tipo:= 'another';
		      END IF;
		      
		      --ATRIBUTES
		      v_nome_coluna := v_cursor_colunas.coluna;
		      v_classe := v_classe || 'private ' || v_tipo || ' _' || v_cursor_colunas.coluna || ';' || E'\r\n';

		      --PROPERTIES
		      v_classe := v_classe || 'public ' || v_tipo || ' ' || v_cursor_colunas.coluna || '{' || E'\r\n';
		      v_classe := v_classe || '  get {' || ' return _' || v_cursor_colunas.coluna || '; }' || E'\r\n';
		      v_classe := v_classe || '  set {' || ' _' || v_cursor_colunas.coluna || ' = value; }' || E'\r\n';
		      v_classe := v_classe || '}' || E'\r\n';
		      
		   END LOOP;
		   v_classe := v_classe || E'\r\n' || '}';
		   
		  RAISE NOTICE '%' , v_classe;

		--raise notice '%' , v_print_tablename;
		cnt := cnt + 1 ; 


	 END LOOP ; 

END $$;

MS SQL 快速產生 C# 類別

https://habilisbest.com/creating-c-model-class-from-sql-query

 

DECLARE @cnt INT; 

SET @cnt = 1; 

DECLARE @tableCount INT; 

SET @tableCount = (SELECT Count(*) 
                   FROM   sys.tables 
                   WHERE  type = 'U') 

WHILE @cnt <= @tableCount 
BEGIN
	--逐一取得資料表的名稱
	declare @tbName nvarchar(200);
	set @tbName = (
	SELECT name FROM
	(SELECT  ROW_NUMBER() OVER(ORDER BY name) AS ROWID , name
	FROM sys.Tables
	WHERE type = 'U') mytable
	WHERE ROWID = @cnt)

--原作者的 code 產生 csharp 類別
--這段不要 format 產出來才好看
declare @TableName sysname = @tbName
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
	public ' + (CASE WHEN ColumnName = 'RowVersion' THEN 'byte[]' ELSE ColumnType END) + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
	select 
		replace(col.name, ' ', '_') ColumnName,
		column_id ColumnId,
		case typ.name 
			when 'bigint' then 'long'
			when 'binary' then 'byte[]'
			when 'bit' then 'bool'
			when 'char' then 'string'
			when 'date' then 'DateTime'
			when 'datetime' then 'DateTime'
			when 'datetime2' then 'DateTime'
			when 'datetimeoffset' then 'DateTimeOffset'
			when 'decimal' then 'decimal'
			when 'float' then 'float'
			when 'image' then 'byte[]'
			when 'int' then 'int'
			when 'money' then 'decimal'
			when 'nchar' then 'string'
			when 'ntext' then 'string'
			when 'numeric' then 'decimal'
			when 'nvarchar' then 'string'
			when 'real' then 'double'
			when 'smalldatetime' then 'DateTime'
			when 'smallint' then 'short'
			when 'smallmoney' then 'decimal'
			when 'text' then 'string'
			when 'time' then 'TimeSpan'
			when 'timestamp' then 'timestamp'
			when 'rowversion' then 'byte[]'
			when 'tinyint' then 'byte'
			when 'uniqueidentifier' then 'Guid'
			when 'varbinary' then 'byte[]'
			when 'varchar' then 'string'
			else 'UNKNOWN_' + typ.name
		end ColumnType,
		case 
			when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
			then '?' 
			else '' 
		end NullableSign
	from sys.columns col
		join sys.types typ on
			col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
	where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

	print @Result

	--遞增
	set @cnt += 1
END

Cesium Terrain 建置

參考文件

https://github.com/geo-data/cesium-terrain-builder

https://hub.docker.com/r/homme/cesium-terrain-builder/

https://github.com/tum-gis/cesium-terrain-builder-docker

我在 ubuntu 上面編譯好多次都失敗,最後不得不妥協使用 docker

要特別注意的是他有分新舊版本,舊版是不能 work 的,雷的很痛苦

舊版

docker pull homme/cesium-terrain-builder

新版

sudo docker pull tumgis/ctb-quantized-mesh

接著我們必須去下載 SRTM 資料集,因為全球的檔案太大我先下載台灣部分區塊測試

SRTM 90m Digital Elevation Data

看網路上這篇說明,需要把 float 轉為 int,並且把 no_data 補成 0 不然會發佈失敗,不過他這篇是 ArcGIS 做出來的,窮人只好用 gdal。

gdal_translate -of GTiff -ot Int32 -a_nodata 0 D:\srtm_61_08\srtm_61_08.tif D:\srtm_61_08\srtm_61_08_new.tif

轉換完畢後執行 docker 開始計算 terrain 這個步驟會非常久

sudo docker run -it -v /home/gg/test_terrain:/data tumgis/ctb-quantized-mesh
ctb-tile -f Mesh -C -N -o ./tw ./srtm_61_08_new.tif

最後就是新版才有的 -l 參數,他會生成一個 layer.json,一定要有這個 json 檔 Cesium 才可以讀取到 terrain 的結構

ctb-tile -f Mesh -C -N -o ./tw -l ./srtm_61_08_new.tif

接著需要寫一些 C# 的 code 並且把編譯完成的 dll 放在需要佈署的網站的 bin 目錄裡面,這個地方是最關鍵的,卡了很久,可以參考這篇說明 (如果照著他的設定 web.config 會錯誤)。

最主要的原理就是任何 web server 在接收 request 為 terrain 檔時需要在 header 加上 Content-Encoding gzip

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace AddZipHeaderModule
{
    public class ZipHeaderModule : IHttpModule
    {
        public void Dispose()
        {
            //do nothing
            //throw new NotImplementedException();
        }

        public void Init(HttpApplication context)
        {
            context.EndRequest += Context_EndRequest;
        }

        private void Context_EndRequest(object sender, EventArgs e)
        {
            var context = sender as HttpApplication;
            string fileExtension = context.Request.CurrentExecutionFilePathExtension;
            if (fileExtension.Length >= 8)
            {
                if (fileExtension.Substring(0, 8) == ".terrain")
                {
                    context.Response.AddHeader("Content-Encoding", "gzip");
                }
            }
            
        }
    }
}

搞定後要設定 web.config 如下,最後就大功告成了

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
		<validation validateIntegratedModeConfiguration="false" />
		<modules runAllManagedModulesForAllRequests="true">
			<add name="AddZipHeaderModule" type="AddZipHeaderModule.ZipHeaderModule" />
		</modules>
        <httpProtocol>
            <customHeaders>
                <remove name="Access-Control-Allwo-Origin" />
                <add name="Access-Control-Allow-Origin" value="*" />
            </customHeaders>
        </httpProtocol>
    <staticContent>
      <remove fileExtension=".czml" />
      <mimeMap fileExtension=".czml" mimeType="application/json" />
      <remove fileExtension=".glsl" />
      <mimeMap fileExtension=".glsl" mimeType="text/plain" />
      <remove fileExtension=".b3dm" />
      <mimeMap fileExtension=".b3dm" mimeType="application/octet-stream" />
      <remove fileExtension=".pnts" />
      <mimeMap fileExtension=".pnts" mimeType="application/octet-stream" />
      <remove fileExtension=".i3dm" />
      <mimeMap fileExtension=".i3dm" mimeType="application/octet-stream" />
      <remove fileExtension=".cmpt" />
      <mimeMap fileExtension=".cmpt" mimeType="application/octet-stream" />
      <remove fileExtension=".gltf" />
      <mimeMap fileExtension=".gltf" mimeType="model/gltf+json" />
      <remove fileExtension=".bgltf" />
      <mimeMap fileExtension=".bgltf" mimeType="model/gltf-binary" />
      <remove fileExtension=".glb" />
      <mimeMap fileExtension=".glb" mimeType="model/gltf-binary" />
      <remove fileExtension=".json" />
      <mimeMap fileExtension=".json" mimeType="application/json" />
      <remove fileExtension=".geojson" />
      <mimeMap fileExtension=".geojson" mimeType="application/json" />
      <remove fileExtension=".topojson" />
      <mimeMap fileExtension=".topojson" mimeType="application/json" />
      <remove fileExtension=".woff" />
      <mimeMap fileExtension=".woff" mimeType="application/font-woff" />
      <remove fileExtension=".woff2" />
      <mimeMap fileExtension=".woff2" mimeType="application/font-woff2" />
      <remove fileExtension=".kml" />
      <mimeMap fileExtension=".kml" mimeType="application/vnd.google-earth.kml+xml" />
      <remove fileExtension=".kmz" />
      <mimeMap fileExtension=".kmz" mimeType="application/vnd.google-earth.kmz" />
      <remove fileExtension=".svg" />
      <mimeMap fileExtension=".svg" mimeType="image/svg+xml" />
      <remove fileExtension=".terrain" />
      <mimeMap fileExtension=".terrain" mimeType="application/vnd.quantized-mesh" />
      <remove fileExtension=".ktx" />
      <mimeMap fileExtension=".ktx" mimeType="image/ktx" />
      <remove fileExtension=".crn" />
      <mimeMap fileExtension=".crn" mimeType="image/crn" />
      <remove fileExtension=".webm" />
      <mimeMap fileExtension=".webm" mimeType="video/webm" />
    </staticContent>
    </system.webServer>
</configuration>

後記

有種相見恨晚的感覺

http://sunhongyi.com/

定義 sld 樣式 使用 NCL color table

接續之前的 sld 燙手山芋,需要定義多組 raster 的樣式,所幸 NCL 官方有提供非常多組色表可以下載。載回來後是一個 .rgb 的纇 csv 檔案,我用 csharp 的 console 寫了一個轉換程式好讓其變成 16 進位的表,詳細過程早就忘光光等腦子清楚再寫。

static void Main(string[] args)
        {
            //讀取color文字檔
            //name , r , g , b
            //轉換 rgb 為 hex
            var files = Directory.EnumerateFiles(@"D:\rgb\", "*.rgb");
            
            foreach (var file in files)
            {
                
                var fileName = Path.GetFileNameWithoutExtension(file);
                

                var lines = File.ReadAllLines(@"D:\rgb\" + fileName + ".rgb");
                List<string> colorList = new List<string>();



                //寫入檔案
                var flag = false;
                var skipColorCount = 0;
                List<string> hexs = new List<string>();

                int skip = 2;
                int counter = 1;
                foreach (var line in lines)
                {
                    if (counter <= skip)
                    {
                        counter++;
                        continue;
                    }

                    flag = !flag;
                    if (flag == false)
                    {
                        continue;
                    }


                    //skipColorCount++;
                    //if (skipColorCount % 16 != 0)
                    //{
                    //    continue;
                    //}


                    var lineResult = line.Split(' ');
                    //string name = lineResult[0];
                    int r = Convert.ToInt32(Convert.ToDouble(lineResult[0]) * 255.0);
                    int g = Convert.ToInt32(Convert.ToDouble(lineResult[1]) * 255.0);
                    int b = Convert.ToInt32(Convert.ToDouble(lineResult[2]) * 255.0);
                    Color color = Color.FromArgb(r, g, b);

                    string hex = color.R.ToString("X2") + color.G.ToString("X2") + color.B.ToString("X2");


                    hexs.Add(hex);

                    //writer.WriteLine("#{0}", hex);

                    //writer.WriteLine(string.Format("<option value='{0}' style='background: #{1};'>{0}</option>", name, hex));


                    

                }


                using (StreamWriter writer = new StreamWriter(@"D:\rgb\" + fileName + ".txt"))
                {
                    foreach (var hex in hexs)
                    {
                        writer.WriteLine("#{0}", hex);
                    }
                }

                hexs.Reverse();
                using (StreamWriter writer = new StreamWriter(@"D:\rgb\" + fileName + "-inv.txt"))
                {
                    foreach (var hex in hexs)
                    {
                        writer.WriteLine("#{0}", hex);
                    }
                }
            }


        }

以下提供一些現成的 64 色 colormap ,可以直接套在 ncWMS 或 GeoServer 使用

Rainbow

#7E03FF
#7510FF
#6E1CFF
#6529FE
#5E35FE
#5541FD
#4E4DFC
#4559FB
#3D65FA
#376DF9
#2D7BF7
#2884F6
#1D91F3
#1898F2
#0DA5EF
#08ACEE
#03B7EB
#0AC0E8
#12C8E6
#1BCFE3
#22D6E0
#2BDDDD
#32E3DA
#3BE8D6
#42EDD3
#4BF2CF
#52F5CB
#5BF8C8
#63FBC4
#6AFDC0
#73FEBB
#7AFFB7
#83FFB3
#8AFEAE
#93FDA9
#9AFBA5
#A3F9A0
#AAF69B
#B2F396
#BBEE91
#C2EA8C
#CBE486
#D2DE81
#DBD87B
#E2D176
#EBCA70
#F2C26B
#FBB965
#FFB05F
#FFA759
#FF9D53
#FF934D
#FF8947
#FF7E41
#FF733B
#FF6835
#FF5C2F
#FF5029
#FF4422
#FF381C
#FF2C16
#FF1F10
#FF1309
#FF0603

Jet

#000084
#000096
#0000A8
#0000BB
#0000CD
#0000DF
#0000F1
#0000FF
#0004FF
#0011FF
#0024FF
#0030FF
#0044FF
#0050FF
#0065FF
#0070FF
#0085FF
#0095FF
#00A4FF
#00B4FF
#00C4FF
#00D4FF
#00E4F8
#0CF5EB
#19FFDE
#26FFD1
#33FFC4
#40FFB7
#4DFFAA
#5AFF9D
#66FF90
#73FF83
#80FF77
#8DFF6A
#9AFF5D
#A7FF50
#B4FF43
#C1FF36
#CEFF29
#DBFF1C
#E7FF0F
#F4F802
#FFEA00
#FFDB00
#FFCC00
#FFBD00
#FFAE00
#FF9F00
#FF9100
#FF8200
#FF7300
#FF6400
#FF5500
#FF4700
#FF3800
#FF2900
#FF1A00
#F60B00
#E40000
#D10000
#BF0000
#AD0000
#9B0000
#890000

Ocean

#007E01
#007805
#007209
#006C0D
#006611
#006015
#005A19
#00541D
#004E21
#004924
#004229
#003D2C
#003631
#003234
#002A39
#00263C
#001E41
#001845
#001249
#000C4D
#000651
#000055
#000659
#000C5D
#001261
#001865
#001E69
#00246D
#002A71
#003075
#003679
#003C7D
#004281
#004885
#004E89
#00548D
#005A91
#006095
#006699
#006C9D
#0072A1
#0078A5
#007EA9
#0984AD
#158AB1
#2190B5
#2D96B9
#399CBD
#45A2C1
#51A8C5
#5DAEC9
#69B4CD
#75BAD1
#81C0D5
#8DC6D9
#99CCDD
#A5D2E1
#B1D8E5
#BDDEE9
#C9E4ED
#D5EAF1
#E1F0F5
#EDF6F9
#F9FCFD

 

CoolWarm

#3C4EC2
#4055C8
#455CCE
#4A63D3
#4F69D9
#5470DE
#5977E3
#5E7DE7
#6384EB
#6788EE
#6E90F2
#7295F4
#799CF8
#7DA0F9
#84A7FC
#88ABFD
#8FB1FE
#94B6FF
#9ABBFF
#9FBFFF
#A5C3FE
#AAC7FD
#AFCAFC
#B5CDFA
#BAD0F8
#BFD3F6
#C4D5F3
#C9D7F0
#CDD9EC
#D2DBE8
#D6DCE4
#DADCE0
#DEDCDB
#E2DAD5
#E6D7CF
#EAD5C9
#EDD2C3
#EFCEBD
#F2CBB7
#F3C7B1
#F5C2AA
#F6BEA4
#F7B99E
#F7B497
#F7AF91
#F7A98B
#F6A385
#F59D7E
#F39778
#F29072
#F08A6C
#ED8366
#EA7B60
#E7745B
#E36C55
#E0654F
#DC5D4A
#D75445
#D24B40
#CD423B
#C83836
#C32E31
#BD1F2D
#B70D28

 

 

使用 XSD 檔產生 C# 類別

某次工作中遇到了老外一個控制 OX 硬體的程式,分別會在 Input , Output 兩個資料夾內產生 XML 檔案,這種做法大多是 10 年前 XML 格式盛行時流行的方法。該軟體沒有任何 SDK 所以就算有 XML 結構也很難完整解析他內部的邏輯與全功能,因此我研究了一下他內部檔案有 XSD 定義檔,看了微軟官方文件後確定可以產生 C# 類別就可以減輕開發負擔,後續又在莫名其妙的工作中遇到類似的事,這招真的滿有用滴。

此外 xml 也可以直接轉換為 xsd 可以參考這網站

https://www.freeformatter.com/xsd-generator.html

這是我當時的作業環境 (OS:win10 , netframework 4.5.1)

cd C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools
xsd "C:\Program Files (x86)\OXOXOX\Schema.xsd" /classes /o:D:\