.NET Core项目上传.shp至PostGIS数据库的实现方案及坐标系检测求助

Hey there! Since you already have your map display working and know how to use shp2pgsql for test data, let's tackle the upload flow and coordinate system detection you're missing. Here's a practical breakdown with code examples tailored to .NET Core.

1. Core Workflow Overview #

First, let's outline the key steps you'll need to implement:

• Accept a ZIP archive from the user (Shapefiles require multiple files: .shp, .shx, .dbf, often .prj for coordinates)
• Extract the archive to a temporary directory
• Validate the required files are present
• Detect the coordinate system from the .prj file (if available)
• Import the Shapefile data into PostGIS (either via shp2pgsql CLI or a .NET spatial library)
• Clean up temporary files and return success/metadata to your frontend

2. Required NuGet Packages #

Add these to your project to handle spatial data, PostGIS integration, and file handling:

Install-Package Npgsql.EntityFrameworkCore.PostGIS
Install-Package NetTopologySuite.IO.ShapeFile
Install-Package ProjNet.CoordinateSystems  # For parsing .prj files
Install-Package SharpZipLib  # For extracting ZIP archives

3. Code Implementation #

3.1 Controller Action to Receive Upload #

First, create an endpoint to accept the ZIP file:

using Microsoft.AspNetCore.Mvc;
using System.IO;
using ICSharpCode.SharpZipLib.Zip;

[ApiController]
[Route("api/shapefile")]
public class ShapefileUploadController : ControllerBase
{
    private readonly IConfiguration _config;
    private readonly ILogger<ShapefileUploadController> _logger;

    public ShapefileUploadController(IConfiguration config, ILogger<ShapefileUploadController> logger)
    {
        _config = config;
        _logger = logger;
    }

    [HttpPost("upload")]
    public async Task<IActionResult> UploadShapefile(IFormFile zipFile)
    {
        if (zipFile == null || zipFile.Length == 0)
            return BadRequest("No file uploaded.");

        // Create temp directory to extract ZIP
        var tempDir = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString());
        Directory.CreateDirectory(tempDir);

        try
        {
            // Extract ZIP content
            using (var stream = zipFile.OpenReadStream())
            using (var zipInputStream = new ZipInputStream(stream))
            {
                ZipEntry entry;
                while ((entry = zipInputStream.GetNextEntry()) != null)
                {
                    var entryPath = Path.Combine(tempDir, entry.Name);
                    // Ensure subdirectories are created
                    Directory.CreateDirectory(Path.GetDirectoryName(entryPath));

                    using (var fileStream = File.Create(entryPath))
                    {
                        await zipInputStream.CopyToAsync(fileStream);
                    }
                }
            }

            // Validate required Shapefile components exist
            var shpPath = Directory.EnumerateFiles(tempDir, "*.shp").FirstOrDefault();
            var shxPath = Directory.EnumerateFiles(tempDir, "*.shx").FirstOrDefault();
            var dbfPath = Directory.EnumerateFiles(tempDir, "*.dbf").FirstOrDefault();
            var prjPath = Directory.EnumerateFiles(tempDir, "*.prj").FirstOrDefault();

            if (shpPath == null || shxPath == null || dbfPath == null)
                return BadRequest("Invalid Shapefile: Missing .shp, .shx, or .dbf file.");

            // 1. Detect Coordinate System
            var coordinateSystemInfo = await DetectCoordinateSystem(prjPath);

            // 2. Import to PostGIS
            var importSuccess = await ImportShapefileToPostGIS(shpPath, coordinateSystemInfo.EpsgCode);

            if (importSuccess)
                return Ok(new { 
                    Message = "Shapefile uploaded successfully.",
                    CoordinateSystem = coordinateSystemInfo.Name,
                    EpsgCode = coordinateSystemInfo.EpsgCode
                });
            else
                return StatusCode(500, "Failed to import Shapefile to PostGIS.");
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error processing Shapefile upload.");
            return StatusCode(500, "An error occurred during upload.");
        }
        finally
        {
            // Clean up temp directory
            if (Directory.Exists(tempDir))
                Directory.Delete(tempDir, recursive: true);
        }
    }
}

3.2 Coordinate System Detection #

Here's how to parse the .prj file to get the EPSG code and coordinate system name:

using ProjNet.CoordinateSystems;
using ProjNet.CoordinateSystems.Transformations;

private async Task<(string Name, int? EpsgCode)> DetectCoordinateSystem(string prjPath)
{
    if (string.IsNullOrEmpty(prjPath) || !File.Exists(prjPath))
        return ("Unknown (no .prj file)", null);

    var prjContent = await File.ReadAllTextAsync(prjPath);
    var csFactory = new CoordinateSystemFactory();
    var cs = csFactory.CreateFromWkt(prjContent);

    // Try to get EPSG code using ProjNet's authority factory
    var authFactory = new AuthorityFactory("EPSG", new CoordinateSystemFactory());
    int? epsgCode = null;
    try
    {
        epsgCode = authFactory.EpsgCodeFromCoordinateSystem(cs);
    }
    catch
    {
        // If EPSG code can't be resolved, return WKT description
        return (cs.Name, null);
    }

    return (cs.Name, epsgCode);
}

3.3 Import to PostGIS (Two Options) #

Option A: Use shp2pgsql CLI (Familiar to You)

Since you've used shp2pgsql before, this is a straightforward approach. We'll execute the CLI command and pipe the output to psql:

using System.Diagnostics;

private async Task<bool> ImportShapefileToPostGIS(string shpPath, int? epsgCode)
{
    var connString = _config.GetConnectionString("PostGIS");
    var dbName = _config["PostGIS:DatabaseName"];
    var tableName = $"uploaded_{Guid.NewGuid().ToString().Replace("-", "")}"; // Unique table name

    // Build shp2pgsql command: adjust options as needed (-s for EPSG code, -I to create spatial index)
    var shp2pgsqlArgs = $"-s {epsgCode ?? 4326} -I \"{shpPath}\" public.{tableName}";
    
    // Execute shp2pgsql and pipe output to psql
    var process = new Process
    {
        StartInfo = new ProcessStartInfo
        {
            FileName = "cmd.exe",
            Arguments = $"/c shp2pgsql {shp2pgsqlArgs} | psql {connString}",
            UseShellExecute = false,
            CreateNoWindow = true,
            RedirectStandardError = true
        }
    };

    process.Start();
    var errorOutput = await process.StandardError.ReadToEndAsync();
    await process.WaitForExitAsync();

    if (process.ExitCode != 0)
    {
        _logger.LogError("Import Error: {Error}", errorOutput);
        return false;
    }

    // Optional: Store table name in your app's metadata so your map can query it
    return true;
}

Option B: Use .NET Spatial Libraries (No CLI Dependency)

If you prefer a pure .NET approach, use NetTopologySuite to read the Shapefile and bulk insert into PostGIS via EF Core:

using NetTopologySuite.IO;
using Microsoft.EntityFrameworkCore;

// First, define a DbContext with PostGIS support
public class PostGISContext : DbContext
{
    public DbSet<SpatialFeature> SpatialFeatures { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connString = _config.GetConnectionString("PostGIS");
        optionsBuilder.UseNpgsql(connString, o => o.UseNetTopologySuite());
    }
}

// Entity class for spatial features (adjust attributes to match your Shapefile)
public class SpatialFeature
{
    public int Id { get; set; }
    public Dictionary<string, object> Attributes { get; set; } = new();
    public NetTopologySuite.Geometries.Geometry Geometry { get; set; }
}

// Import method
private async Task<bool> ImportShapefileToPostGIS(string shpPath, int? epsgCode)
{
    var reader = new ShapefileDataReader(shpPath, GeometryFactory.Default);
    var attributeNames = reader.DbaseHeader.Fields.Select(f => f.Name).ToList();

    using var context = new PostGISContext();
    await context.Database.BeginTransactionAsync();

    try
    {
        while (reader.Read())
        {
            var geometry = reader.Geometry;
            // Transform geometry to PostGIS's default (usually EPSG:4326) if needed
            if (epsgCode.HasValue && epsgCode != 4326)
            {
                var transform = new CoordinateTransformationFactory().CreateFromCoordinateSystems(
                    AuthorityFactory.CreateCoordinateSystem($"EPSG:{epsgCode}"),
                    AuthorityFactory.CreateCoordinateSystem("EPSG:4326")
                );
                geometry = transform.MathTransform.Transform(geometry);
            }

            // Populate attributes
            var attributes = attributeNames.ToDictionary(name => name, name => reader[name]);
            context.SpatialFeatures.Add(new SpatialFeature { Geometry = geometry, Attributes = attributes });
        }

        await context.SaveChangesAsync();
        await context.Database.CommitTransactionAsync();
        return true;
    }
    catch (Exception ex)
    {
        await context.Database.RollbackTransactionAsync();
        _logger.LogError(ex, "Error inserting features to PostGIS.");
        return false;
    }
}

4. Key Process Recommendations #

• ZIP Uploads Mandatory: Never accept individual Shapefile files—users often forget to upload all required components. Enforce ZIP uploads to avoid missing .shx/.dbf errors.
• Temporary File Cleanup: Always delete temp directories after processing to avoid disk bloat (the finally block in the controller handles this).
• Error Handling: Add checks for invalid file types, corrupted archives, and PostGIS connection failures. Return meaningful error messages to the frontend.
• Spatial Indexes: Use the -I flag with shp2pgsql or add indexes via EF Core to ensure fast map queries.
• Coordinate System Fallback: If no .prj file exists, default to EPSG:4326 (WGS84) and inform the user that the coordinate system is unknown.
• Table Naming: Use unique, descriptive table names (like uploaded_<guid>) to avoid conflicts between different user uploads.

5. Can We Detect the Shapefile's Coordinate System? #

Absolutely! As shown in the DetectCoordinateSystem method above, you can read the .prj file (which contains WKT for the coordinate system) and use libraries like ProjNet to resolve it to an EPSG code. If the .prj file is missing, you'll have to default to a known system (like 4326) or ask the user to specify it.

内容的提问来源于stack exchange，提问作者Frets